所有字段都具有相同的类型值sql server。

Question

我有一张表，我想要的统计价格类型a和类型c作为销售，类型b和类型d作为购买日，结果如下

 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和类型a，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

试试这个

我在你的桌子上看不到时间，但我想列日是日期格式的

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'

这样，只有当两者都是你所需要的时候，它才会加起来。