如何在 SQL GROUP BY 子句中返回空组 [英] How to return empty groups in SQL GROUP BY clause

问题描述

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

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

这篇关于如何在 SQL GROUP BY 子句中返回空组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！