在 group by 字段中使用 case 语句的 SQL 查询

Question

提问by saspert

I have a complex query that joins different tables to get the count. There are a few fields to group by. Now, I want to add an additional field which needs a case statement. And this field also has to be in the group by list. My query originally looks like this -

我有一个复杂的查询，它连接不同的表来获取计数。有几个字段可以分组。现在，我想添加一个需要 case 语句的附加字段。而且这个字段也必须在 group by 列表中。我的查询最初看起来像这样 -

SELECT DMAGATR.WRK_LOC_LEVEL4 
     , DMBR.WRK_LOC_NM 
     , DMBR.RELCD 
     , COUNT(DISTINCT DMBR.DMBRKEY) AS ELIG_COUNT
      FROM DMBR 
INNER JOIN DCUST DCUST ON DMBR.DCUSTKEY = DCUST.DCUSTKEY
INNER JOIN DMAGATR DMAGATR ON DMBR.DMBRKEY = DMAGATR.DMBRKEY
 LEFT JOIN DMDYNATR DMDYNATR ON DMBR.DMBRKEY = DMDYNATR.DMBRKEY
     WHERE DMBR.C_TIMESSTAMP <= '12/31/2011'
       AND DMBR.RELCD IN ('0', '1') 
       AND DMBR.EE_STS IN ( 'A','L')
       AND (DMBR.DEL_DT IS NULL
        OR DMBR.DEL_DT > '12/31/2011')
       AND DCUST.PRCD = 'TAR'
  GROUP BY DMAGATR.WRK_LOC_LEVEL4, DMBR.WRK_LOC_NM, D_MEMBER.REL_CD

But the new field looks something like this -

但新领域看起来像这样——

(SELECT CASE
          WHEN (DMBR.WRK_LOC_NM = '6' AND DMBR.GDR = 'M' AND DMBR.REL_CD in ('0','1') 
            AND DMBR.EE_STS IN ('A','L')) THEN 'SEG 1'
          ELSE 'OTHER'
        END 
   FROM DMBR) as CMPN

I tried to add it in the select list but it did not work. Then I added it in two places - in the select and also in the group by list. That did not work either.

我试图将它添加到选择列表中，但它不起作用。然后我在两个地方添加了它 - 在选择和分组列表中。那也没有用。

The errors I got were:

我得到的错误是：

ORA-00904- CMPN not a valid column
ORACLE prepare error: ORA-22818: subquery expressions not allowed here.

ORA-00904- CMPN 不是有效的列
ORACLE 准备错误::ORA-22818此处不允许使用子查询表达式。

I did some research online found examples that were close but not exactly identical to mine.

我在网上做了一些研究，发现了一些与我的很接近但不完全相同的例子。

SQL GROUP BY CASE statement with aggregate functionNot sure if I understood the question here SQL query with count and case statementThis is quite different from my need. http://jerrytech.blogspot.com/2008/04/can-you-group-by-case-statement-in-sql.html(this is close but I dont need the insert statements I tried this approach but it did not work for me)

带有聚合函数的 SQL GROUP BY CASE 语句不确定我是否理解这里的问题带有计数和 case 语句的 SQL 查询这与我的需要完全不同。 http://jerrytech.blogspot.com/2008/04/can-you-group-by-case-statement-in-sql.html（这很接近但我不需要插入语句我尝试过这种方法但它没有对我来说有效）

Any suggestions would be appreciated.

任何建议，将不胜感激。

Answer 1

采纳答案by DRapp

I think the error is you are describing a FIELD (ie: result column) for the query like the others: DMAGATR.WRK_LOC_LEVEL4 ,DMBR.WRK_LOC_NM ,DMBR.RELCD ,COUNT (DISTINCT DMBR.DMBRKEY...

我认为错误是您像其他人一样描述查询的 FIELD（即：结果列）： DMAGATR.WRK_LOC_LEVEL4 ,DMBR.WRK_LOC_NM ,DMBR.RELCD ,COUNT (DISTINCT DMBR.DMBRKEY...

I think the error is that when using a SQL-Select statement for a resulting COLUMN, it must only return a single row. Since your query is just "... FROM DMBR ) as CMPN", you are returning more than one row for the field and no Database knows how to guess your result.

我认为错误在于当对结果列使用 SQL-Select 语句时，它必须只返回一行。由于您的查询只是“... FROM DMBR ) as CMPN”，您为该字段返回了不止一行，并且没有数据库知道如何猜测您的结果。

What you are probably missing is both a WHERE clause on the field, and possibly a GROUP by if you are looking for a distinct value from within the DMBR table.

您可能缺少的是字段上的 WHERE 子句，如果您正在寻找 DMBR 表中的不同值，则可能缺少 GROUP by。

Fix that and it should get you MUCH further along. Not knowing the rest of data structure or relationships, I can't figure what your ultimate result is meant to be.

解决这个问题，它应该会让你走得更远。不知道其余的数据结构或关系，我无法弄清楚你的最终结果是什么。

ADDITIONAL COMMENT...

附加评论...

By looking at other answers provided, they have offered to do an immediate CASE WHEN on whatever the current "DMBR" record you are on, which would be correct, but not quite working. I think due to the two possible results, that too will have to be part of the group by.. as count(DISTINCT), the group by has to be based on any non-aggregation columns... of which, this case/when would be as such.. So your ultimate result would have

通过查看提供的其他答案，他们提出立即对您当前使用的“DMBR”记录进行处理，这是正确的，但不太有效。我认为由于这两种可能的结果，这也必须是 group by 的一部分......作为 count(DISTINCT)，group by 必须基于任何非聚合列......其中，这种情况/什么时候会这样......所以你的最终结果会是

Lvl, Work Loc, RelCD, Case/when, count(distinct)  where...
                        SEG 1     999
                        Other     999

Additionally, your CASE/WHEN had two components exactly matching your WHERE clause, so I took it out of there since no records of that set would have been returned anyway.

此外，您的 CASE/WHEN 有两个组件与您的 WHERE 子句完全匹配，因此我将其从那里取出，因为无论如何都不会返回该集合的记录。

So, all that being said, I would write it as...

所以，说了这么多，我会把它写成……

SELECT
      DMAGATR.WRK_LOC_LEVEL4,
      DMBR.WRK_LOC_NM,
      DMBR.RELCD,
      CASE WHEN (DMBR.WRK_LOC_NM = '6' 
             AND DMBR.GDR = 'M' ) 
           THEN 'SEG 1'
           ELSE 'OTHER'
           END as WhenStatus,
      COUNT (DISTINCT DMBR.DMBRKEY) AS ELIG_COUNT
   FROM
      DMBR 
         JOIN DCUST 
            ON  DMBR.DCUSTKEY = DCUST.DCUSTKEY
         JOIN DMAGATR
            ON  DMBR.DMBRKEY = DMAGATR.DMBRKEY
         LEFT JOIN DMDYNATR
            ON  DMBR.DMBRKEY = DMDYNATR.DMBRKEY
   WHERE
          DMBR.C_TIMESSTAMP <= '12/31/2011'
      AND DMBR.REL_CD in ('0','1') 
      AND DMBR.EE_STS IN ('A','L')) 
      AND DCUST.PRCD = 'TAR'
      AND (    DMBR.DEL_DT IS NULL
           OR  DMBR.DEL_DT > '12/31/2011')
   GROUP BY 
      DMAGATR.WRK_LOC_LEVEL4,
      DMBR.WRK_LOC_NM,
      D_MEMBER.REL_CD,
      CASE WHEN (DMBR.WRK_LOC_NM = '6' 
            AND DMBR.GDR = 'M' ) 
          THEN 'SEG 1'
          ELSE 'OTHER'
          END

Finally, sometimes, I've seen where a group by will choke on a complex column, such as a case / when. However, different servers allow ordinal reference to the group by (and order by too) positions. So, since the query has 4 non-aggregate columns (all listed first), then the count of distinct, you MIGHT be able to get away with changing the GROUP BY clause to...

最后，有时，我看到 group by 会在复杂的列上窒息，例如 case / when。但是，不同的服务器允许对 group by（和 order by）位置进行顺序引用。因此，由于查询有 4 个非聚合列（首先列出所有列），然后是不同的计数，您可能可以将 GROUP BY 子句更改为...

GROUP BY 1, 2, 3, 4

按 1、2、3、4 分组

All pertaining to the sequential order of columns STARTING the SQL-Select call.

所有都与启动 SQL-Select 调用的列的顺序有关。

--- CLARIFICATION about group by and case-sensitivity

--- 关于分组和区分大小写的澄清

First, the case-sensitivity, most engines are case-sensitive on keywords, hence CASE WHEN ... AND ... THEN ... ELSE ... END.

首先，区分大小写，大多数引擎对关键字区分大小写，因此 CASE WHEN ... AND ... THEN ... ELSE ... END。

As for the "group by" (and also works for the "order by"), its more of a shortcut to the ordinal columns in your query instead of explicitly listing the long names to them and having to re-type the entire CASE construct a second time, you can just let the engine know which column of the result set you want to order by look at the following (unrelated) query...

至于“group by”（也适用于“order by”），它更像是查询中序数列的快捷方式，而不是显式列出它们的长名称并且必须重新键入整个 CASE 构造第二次，您可以通过查看以下（不相关的）查询让引擎知道您要对结果集的哪一列进行排序...

select
      lastname,
      firstname,
      sum( orderAmount ) TotalOrders
   from
      customerOrders
   group by
      lastname,
      firstname
   order by 
     TotalOrders DESC

and

和

select
      lastname,
      firstname,
      sum( orderAmount ) TotalOrders
   from
      customerOrders
   group by
      1,
      2
   order by 
      3 DESC

Each would produce the same results... The fictitious customerOrders table would be pre-aggregated by last name and first name and show the total per person (all assuming no duplicate names for this example, otherwise, I would have used a customer ID). Once that is done, the order by kicks in and will put in order of the most sales to a given customer in DESCENDING order at the top of the list.

每个都会产生相同的结果......虚构的 customerOrders 表将按姓氏和名字预先聚合并显示每个人的总数（所有假设在此示例中没有重复的名称，否则，我会使用客户 ID） . 完成后，order by 开始执行，并将以降序排列给给定客户的最多销售额排列在列表顶部。

The numbers just represent the ordinal columns being returned in the query instead of long-hand typing the field names. More for the issue you have of your "CASE/WHEN" clause to prevent oops retyping and missing it up in the group by and pulling your hair out figuring out why.

数字仅表示查询中返回的有序列，而不是手写字段名称。更多关于你的“CASE/WHEN”子句的问题，以防止oops重新输入并在组中错过它并拉出你的头发找出原因。

Answer 2

回答by Andrew Lazarus

I don't know exactly what you meant by "in the SELECT list". I don't know why CMPN includes its own SELECT. Are you trying to do the following, and if not, what different is the goal?

我不知道你所说的“在 SELECT 列表中”是什么意思。我不知道为什么 CMPN 包括它自己的 SELECT。您是否正在尝试执行以下操作，如果没有，目标有什么不同？

SELECT  
DMAGATR.WRK_LOC_LEVEL4 
,DMBR.WRK_LOC_NM 
,DMBR.RELCD 
,COUNT (DISTINCT DMBR.DMBRKEY) AS ELIG_COUNT
,(CASE
   WHEN (DMBR.WRK_LOC_NM = '6' 
   AND DMBR.GDR = 'M' 
   AND DMBR.REL_CD in ('0','1') 
   AND DMBR.EE_STS IN ('A','L')) 
 THEN 'SEG 1'
 ELSE 'OTHER'
END     
) as CMPN
FROM DMBR 
INNER JOIN  DCUST DCUST
    ON  DMBR.DCUSTKEY = DCUST.DCUSTKEY
INNER JOIN   DMAGATR DMAGATR
    ON  DMBR.DMBRKEY = DMAGATR.DMBRKEY
LEFT JOIN   DMDYNATR DMDYNATR
    ON  DMBR.DMBRKEY = DMDYNATR.DMBRKEY
WHERE    DMBR.C_TIMESSTAMP <= '12/31/2011'
AND DMBR.RELCD IN ('0', '1') 
AND DMBR.EE_STS IN ( 'A','L')
AND (DMBR.DEL_DT IS NULL
OR  DMBR.DEL_DT > '12/31/2011')
AND     DCUST.PRCD = 'TAR'
GROUP BY 
     DMAGATR.WRK_LOC_LEVEL4
    ,DMBR.WRK_LOC_NM
    ,D_MEMBER.REL_CD
    ,(CASE
   WHEN (DMBR.WRK_LOC_NM = '6' 
   AND DMBR.GDR = 'M' 
   AND DMBR.REL_CD in ('0','1') 
   AND DMBR.EE_STS IN ('A','L')) 
 THEN 'SEG 1'
 ELSE 'OTHER'
END)

Answer 3

回答by ypercube??

You can also try this (derived subquery) approach if the other answers don't work:

如果其他答案不起作用，您也可以尝试这种（派生子查询）方法：

SELECT
      WRK_LOC_LEVEL4,
      WRK_LOC_NM,
      RELCD,
      CMPN,
      COUNT (DISTINCT DMBRKEY) AS ELIG_COUNT
FROM
  ( SELECT
          DMAGATR.WRK_LOC_LEVEL4,
          DMBR.WRK_LOC_NM,
          DMBR.RELCD,
          CASE WHEN (DMBR.WRK_LOC_NM = '6' 
                 AND DMBR.GDR = 'M' ) 
               THEN 'SEG 1'
               ELSE 'OTHER'
          END
            AS CMPN,
          DMBR.DMBRKEY
       FROM
          DMBR 
             JOIN DCUST 
                ON  DMBR.DCUSTKEY = DCUST.DCUSTKEY
             JOIN DMAGATR
                ON  DMBR.DMBRKEY = DMAGATR.DMBRKEY
             LEFT JOIN DMDYNATR
                ON  DMBR.DMBRKEY = DMDYNATR.DMBRKEY
       WHERE
              DMBR.C_TIMESSTAMP <= '12/31/2011'
          AND DMBR.REL_CD in ('0','1') 
          AND DMBR.EE_STS IN ('A','L')) 
          AND DCUST.PRCD = 'TAR'
          AND (    DMBR.DEL_DT IS NULL
               OR  DMBR.DEL_DT > '12/31/2011')
  ) AS TMP
GROUP BY 
   WRK_LOC_LEVEL4,
   WRK_LOC_NM,
   REL_CD,
   CMPN

Answer 4

回答by Hector Sanchez

    SELECT  
    DMAGATR.WRK_LOC_LEVEL4 
    ,DMBR.WRK_LOC_NM 
    ,DMBR.RELCD 
    ,COUNT (DISTINCT DMBR.DMBRKEY) AS ELIG_COUNT,
    (SELECT 
     CASE
    WHEN (DMBR.WRK_LOC_NM = '6' 
    AND DMBR.GDR = 'M' 
    AND DMBR.REL_CD in ('0','1') 
    AND DMBR.EE_STS IN ('A','L')) 
    THEN 'SEG 1'
    ELSE 'OTHER'
    END     
    ) as CMPN
    FROM DMBR 
    INNER JOIN  DCUST DCUST
        ON  DMBR.DCUSTKEY = DCUST.DCUSTKEY
    INNER JOIN   DMAGATR DMAGATR
        ON  DMBR.DMBRKEY = DMAGATR.DMBRKEY
    LEFT JOIN   DMDYNATR DMDYNATR
        ON  DMBR.DMBRKEY = DMDYNATR.DMBRKEY
    WHERE    DMBR.C_TIMESSTAMP <= '12/31/2011'
    AND DMBR.RELCD IN ('0', '1') 
    AND DMBR.EE_STS IN ( 'A','L')
    AND (DMBR.DEL_DT IS NULL
    OR  DMBR.DEL_DT > '12/31/2011')
    AND     DCUST.PRCD = 'TAR'
    GROUP BY 
    DMAGATR.WRK_LOC_LEVEL4
    ,DMBR.WRK_LOC_NM
    ,D_MEMBER.REL_CD,  DMBR.GDR, DBMR.EE_STS

在 group by 字段中使用 case 语句的 SQL 查询

提问by saspert

采纳答案by DRapp

回答by Andrew Lazarus

回答by ypercube??

回答by Hector Sanchez

相关推荐

最近更新

标签

在 group by 字段中使用 case 语句的 SQL 查询

提问by saspert

采纳答案by DRapp

回答by Andrew Lazarus

回答by ypercube??

回答by Hector Sanchez

相关推荐

SQL 基于另一个表更新表

SQL 导出数据库的 CREATE 脚本

T-SQL：删除所有重复行但保留一个

SQL ORA-00904: 无效标识符

相关推荐

最近更新

标签