sql-server - T-SQL CASE/子查询

Question

下面有一个基于日期返回结果的 T-SQL 查询

SELECT
    SUM(CASE WHEN date ='2012-10-31' then Amount ELSE 0 END) AS [Amount],
    SUM(CASE WHEN date ='2012-10-31' then Discount1  ELSE 0 END) AS [Discount 1],
    SUM(CASE WHEN date ='2012-10-31' then Discount2  ELSE 0 END) AS [Discount 2]
    SUM(CASE WHEN date ='2012-10-31' then Amount - Discount1 - Discount2 ELSE 0 END) AS   
[Total Amount]
FROM
    Orders

当前结果:

Amount   Discount1   Discount2   Total
--------------------------------------  
100.00   5.00        5.00        90.00

我想按月运行和显示/分组。有什么想法吗？

                         Amount    Discount1  Discount2   Total
                       -------------------------------------------
  October                100.00     5.00        5.00       90.00
  November               100.00    10.00        5.00       85.00
  December               200.00    20.00       10.00      170.00 

Answer 1

如果你需要按月分组，你可以试试类似这样:

select datename(mm, date) month,
  sum(amount) amount,
  sum(discount1) discount1,
  sum(discount2) discount2,
  sum(amount - discount1 - discount2) Total
from orders
group by datename(mm, date)

参见 SQL Fiddle with Demo