SUM 所有字段都具有相同的类型值 SQL 服务器

Question

我有一个表格，我想统计 a 型和 c 型的价格为 SELL，类型 b 和类型 d 作为 BUY 按天的结果，结果像

 Table :
   | Name |   type  |   price  |   day       |
   | n1   |   a     |     1    |2013-10-10   |
   | n2   |   b     |     2    |2013-10-10   |
   | n3   |   c     |     3    |2013-10-10   |
   | n4   |   d     |     4    |2013-10-10   |
   | n1   |   a     |     5    |2013-11-10   |
   | n2   |   b     |     6    |2013-11-10   |
   | n3   |   c     |     7    |2013-11-10   |
   | n4   |   d     |     8    |2013-11-10   |

我想要这样的结果

|  DAY       |    SELL (price a+price c)   |   BUY (price b +price d) |
|2013-10-10  |       4                     |         6                |
|2013-11-10  |       12                    |         14               |

这是我的代码

ALTER proc [dbo].[sp_mysp]
@starttime datetime,
 @endtime datetime  
 as
   DECLARE @TIME DATETIME
   SET @TIME=@ENDTIME-@STARTTIME 
   SELECT  (table.day) AS DAY ,  case BUY
   WHEN TABLE.TYPE ='a' or TABLE.TYPE = 'c' THEN SUM (price) end,
   CASE SELL 
   WHEN TABLE.TYPE = 'b' or TABLE.TYPE = 'd' THEN SUM (price) end,
      FROM TABLE WHERE TABLE.DAY>@starttime AND TABLE.TIME<@ENDTIME
      order by TABLE.DAY

GO

我不能随心所欲地求和（a型和c型），求和（b型和d型），它只能按a型和b，b型和b求和......我得到的结果像

 |  DAY       |    SELL (price a+price c)   |   BUY (price b +price d) | 
 | 2013-10-10 |         1                   |       NULL               |
 | 2013-10-10 |         3                   |       NULL               |
 | 2013-10-10 |         NULL                |       2                  |
 | 2013-10-10 |         NULL                |       4                  |
 | 2013-11-10 |         5                   |       NULL               |
 | 2013-11-10 |         7                   |       NULL               |
 | 2013-11-10 |        NULL                 |       6                  |
 | 2013-11-10 |        NULL                 |       8                  |

对我有什么建议吗？对不起，我的英语很糟糕。

Answer 1

试试这个

我在您的表格中看不到时间，但我想 DAY 列是日期格式

ALTER proc [dbo].[sp_mysp]
@starttime datetime,
 @endtime datetime  
 as     
SELECT [Day],
        SUM(CASE WHEN [Type] = 'a' OR [Type] = 'c' THEN price ELSE 0.00 END) SELL,
        SUM(CASE WHEN [Type] = 'b' OR [Type] = 'd' THEN price ELSE 0.00 END) BUY
FROM    [TABLE]
WHERE   [Day] BETWEEN @starttime AND @endtime
GROUP BY    [Day]

Answer 2

我认为你的

TABLE.TYPE ='a' or TABLE.TYPE = 'c'

应该是

TABLE.TYPE ='a' AND TABLE.TYPE = 'c'

这样，只有当两者都是您需要的时，它才会相加。