困住了这个查询

Question

我的数据库看起来像这样

DailyData

rID int
Stock varchar
rDate date
Shares int
price float

我想做的是获得两次约会的数据。

样本数据

rID    stock    rDate        Shares    price
11     Stock1   21/03/2016   15        1.22
12     Stock2   21/03/2016   22        2.23
13     Stock3   21/03/2016   17        3.32
14     Stock4   21/03/2016   10        4.24
15     Stock1   22/03/2016   15        1.25
16     Stock2   22/03/2016   20        2.27
17     Stock3   22/03/2016   17        3.32
18     Stock1   23/03/2016   15        1.28
19     Stock2   23/03/2016   20        2.20
20     Stock3   23/03/2016   17        3.32
21     Stock4   23/03/2016   10        4.24

预期输出

Stock       Shares-21    Shares-20
Stock1      15           15
Stock2      22           20
Stock3      17           17
Stock4      10           0

我对Server数据库的查询：

Select 
    DD1.Stock, sum(DD1.Shares) as Shares-21, sum(DD2.shares) as Shares-20
from 
    DailyData DD1, DailyData DD2
where 
    DD1.rDate = '21/03/2016' and DD2.rDate = '20/03/2016' 
    and DD1.Stock = DD2.Stock
group by 
    DD1.Stock

我得到7行数据，而不是4行数据。

请帮助查询。

*新的修改*我按建议行事，但似乎行不通。这是一个实际的sql脚本。

Select P.pName,DD.Stock, 
  sum(case DD.rDate when '03/21/2016' then DD.Shares  else 0 end) as Shares21, 
  sum(case DD.rDate when '03/20/2016' then DD.Shares else  0 end) as Shares20
from dailyData DD, Portfolios P
where DD.rDate = '03/21/2016' or DD.rDate = '03/20/2016'
  and DD.pID = P.pID
  and DD.pID=1
group by P.pName,DD.stock
order by P.pName,DD.Stock

现在pID=1有23项记录，分别为20-3月和21-3月

在运行此查询时，它返回的值超过23。我只期待23张唱片。

Answer 1

它实际上不需要使用联接。当这样的语句时，您可以使用CASE

SELECT stock, 
       Sum(CASE rdate 
             WHEN '21/03/2016' THEN shares 
             ELSE 0 
           END) AS Shares21, 
       Sum(CASE rdate 
             WHEN '20/03/2016' THEN shares 
             ELSE 0 
           END) AS Shares20 
FROM   dailydata 
WHERE  rdate = '21/03/2016' 
        OR rdate = '20/03/2016' 
GROUP  BY stock 

Answer 2

您可以使用数据透视和DATEPART。

      SELECT Stock, [21] AS [Shares-21], [20] AS [Shares-20] FROM
      (
         SELECT s.Stock, Datepart(d,s.rDate) rDate , s.Shares FROM DailyData s
      ) src
      PIVOT
      (
         MAX(Shares) for rDate in ([21], [20])
      ) pvt