如何在 SQL GROUP BY 子句中返回空组

本文介绍了如何在 SQL GROUP BY 子句中返回空组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个查询要返回每个位置的合同内和合同外花费的金额，返回如下内容:

I have a query to return how much is spent on-contract and off-contract at each location, that returns something like this:

Location | ContractStatus | Expenses -------------+----------------+--------- New York | Ad-hoc | 2043.47 New York | Contracted | 2894.57 Philadelphia | Ad-hoc | 3922.53 Seattle | Contracted | 2522.00

问题是，对于全部是临时费用或全部合同费用的地点，我只能得到一行.我想为每个位置取回两行，如下所示:

The problem is, I only get one row for locations that are all ad-hoc or all contracted expenses. I'd like to get two rows back for each location, like this:

Location | ContractStatus | Expenses -------------+----------------+--------- New York | Ad-hoc | 2043.47 New York | Contracted | 2894.57 Philadelphia | Ad-hoc | 3922.53 Philadelphia | Contracted | 0.00 Seattle | Ad-hoc | 0.00 Seattle | Contracted | 2522.00

有什么办法可以通过 SQL 完成此操作吗?这是我使用的实际查询(SQL Server 2005):

Is there any way I can accomplish this through SQL? Here is the actual query I'm using (SQL Server 2005):

SELECT Location, CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END ORDER BY Location ASC, ContractStatus ASC

推荐答案

是的，构造一个表达式，该表达式只为 adhoc 返回 ordertotal，为其他返回 0，另一个则相反，并对这些表达式求和.这将包括每个位置一行，两列，一列用于临时，一列用于合同......

Yes, construct an expression that returns the ordertotal for adhoc only, and 0 for the others, and another one that does the opposite, and sum those expressions. This will include one row per location with two columns one for adhoc and one for Contracted...

SELECT Location, Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) AdHoc, Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal End) Contracted FROM Orders GROUP BY Location

如果你真的想要为每个单独的行，那么一种方法是:

if you reallly want separate rows for each, then one approach would be to:

SELECT Location, Min('AdHoc') ContractStatus, Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) OrderTotal FROM Orders GROUP BY Location Union SELECT Location, Min('Contracted') ContractStatus, Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal End) OrderTotal FROM Orders GROUP BY Location Order By Location