SQL：针对多个字段使用 Case 语句分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16616346/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Group By with Case Statement for multiple fields
提问by Miggidy
I am trying to write a GROUP BY clause with a CASE statement so I can conditionally GROUP BY according to the value of the parameter in my query. Here is my query that I am using (significantly shortened) and I keep getting "RVPname is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And DVPname is invalid and AEname is invalid.
我正在尝试使用 CASE 语句编写 GROUP BY 子句，以便我可以根据查询中参数的值有条件地 GROUP BY。这是我正在使用的查询（显着缩短）并且我不断收到“RVPname 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。并且 DVPname 无效并且 AEname 无效。
SELECT
DVPname, RVPname, AEname, Dealer, Product, Distribution,
CASE WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END AS NAME
..........other code here.....
GROUP BY Product, Distribution,
CASE WHEN @LEVEL = 'RVP' THEN AEname WHEN @LEVEL = 'DVP' THEN RVPname WHEN @LEVEL= 'AE' THEN Dealer END
--Does anyone know how to do what I am trying to accomplish. When LEVEL='DVP' I want to GROUP BY RVPname, when LEVEL='RVP' I want to GROUP BY AEname....make sense?
- 有谁知道如何做我想要完成的事情。当 LEVEL='DVP' 我想按 RVPname 分组时，当 LEVEL='RVP' 我想按 AEname 分组时......有意义吗？
回答by quantka
A group by query without aggregates (such as your query) will only be useful for removing duplicates. So your options are to add aggregates for columns in the select clause that aren't in the group by or use the query to remove duplicates.
没有聚合的 group by 查询（例如您的查询）仅对删除重复项有用。因此，您的选择是为 select 子句中不在 group by 中的列添加聚合，或使用查询删除重复项。
The following code removes all of the columns (DVPname, RVPname, AEname, Dealer) that are only conditionally in the group by statement (it will hit an error whenever they are not in the group by but are in the select). It also adds the name to the group by so that it can be in the select clause.
以下代码删除了所有仅在 group by 语句中有条件地存在的列（DVPname、RVPname、AEname、Dealer）（只要它们不在 group by 中但在 select 中，就会出错）。它还将名称添加到 group by 以便它可以在 select 子句中。
SELECT
Product, Distribution,
CASE
WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END AS NAME,
CASE
WHEN @LEVEL = 'RVP' THEN AEname
WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL= 'AE' THEN Dealer
END
..........other code here.....
GROUP BY Product, Distribution,
CASE
WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END,
CASE
WHEN @LEVEL = 'RVP' THEN AEname
WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL= 'AE' THEN Dealer
END
回答by Matthew
You will need to select all of your columns that are not in the GROUP BYclause with an aggregate function such as AVG()or SUM(). Otherwise, the database doesn't know what to do with the multiple entries that are returned with grouped records.
您需要GROUP BY使用聚合函数（如AVG()或 ）选择不在子句中的所有列SUM()。否则，数据库不知道如何处理与分组记录一起返回的多个条目。
For example, your select statement should start out with something like this:
例如，您的 select 语句应以如下内容开头：
SELECT
SUM(DVPname), AVG(RVPname),
If they are text entries and you know they are all the same, you can also add they to the GROUP BYclause.
如果它们是文本条目并且您知道它们都相同，您也可以将它们添加到GROUP BY子句中。
GROUP BY DVPname, RVPname, AEname, Dealer, Product, Distribution
In addition, here is a good format for your group by case statement:
此外，对于您的 group by case 语句，这里有一个很好的格式：
GROUP BY
CASE WHEN @SortColumn = '0'
THEN [id] END,
CASE WHEN @SortColumn = '1'
THEN [name] END;
回答by Gordon Linoff
If you just want conditional group bythen I would suggest using a subquery:
如果您只想要条件，group by那么我建议使用子查询：
select name, . ..
from (select (CASE WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END
) AS NAME, t.*
from t
) t
group by name;
However, you lose all the other fields that you want. In most databases, you can't include columns in the selectunless they are aggregated or in the group byclause. Here is an alternative for getting an arbitrary value from the columns:
但是，您将丢失所需的所有其他字段。在大多数数据库中，您不能在 the 中包含列，select除非它们是聚合的或在group by子句中。这是从列中获取任意值的替代方法：
SELECT min(DVPname), min(RVPname), min(AEname), min(Dealer), min(Product),
min(Distribution),
. . .
from (select (CASE WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END
) AS NAME, t.*
from t
) t
group by name;
This returns the min values for the columns.
这将返回列的最小值。
回答by JoeyDG
I know of two methods: GROUP BY (Case statement 1, Case statement 2) and subquery. I determine how many rows each method will take and then base my process on that.
我知道两种方法：GROUP BY（Case statement 1，Case statement 2）和子查询。我确定每种方法将占用多少行，然后以此为基础进行我的处理。
ex. (group by):
前任。（通过...分组）：
SELECT
CASE
WHEN (situation A)
THEN (result A)
WHEN (situation B)
THEN (result B)
END column1,
CASE
WHEN (situation C)
THEN (result C)
WHEN (situation D)
THEN (result D)
END column2,
SUM(AMOUNT) as TotalAMOUNT
FROM aTable
GROUP BY
CASE
WHEN (situation A)
THEN (result A)
WHEN (situation B)
THEN (result B)
END,
CASE
WHEN (situation C)
THEN (result C)
WHEN (situation D)
THEN (result D)
END
ex. (Subquery)
前任。（子查询）
SELECT
Column1,
Column2,
SUM(AMOUNT) as TotalAMOUNT
FROM
(
SELECT
CASE
WHEN (situation A)
THEN (result A)
WHEN (situation B)
THEN (result B)
END column1,
CASE
WHEN (situation C)
THEN (result C)
WHEN (situation D)
THEN (result D)
END column2,
AMOUNT
FROM aTable
)CaseTable
GROUP BY Column1, Column2
ORDER BY Column1 asc, SUM(AMOUNT)
I think important to remember is that you can't contain additional details beyond what you're grouping together. In the above two examples, the results of your query are grouped by the variable you specify. You could also tack on some type of status depending on your variable, and then roll it up by the additional column, and choose not to display the final column in the rollup.
我认为重要的是要记住，除了组合在一起的内容之外，您不能包含其他详细信息。在上面的两个示例中，您的查询结果按您指定的变量分组。您还可以根据您的变量添加某种类型的状态，然后按附加列将其汇总，并选择不显示汇总中的最后一列。
