SQL Server 2000 查询总值和最大日期的值

Question

我有这个有值(value)的房间

SID   Room   Date        APhase   BPhase   ACount  BCount
1     One    10/28/2012  4        5         3       6
2     One    10/29/2012  2        3        -1      -1
3     One    10/30/2012  4        5         7      -1
4     Two    10/28/2012  8        3         2       3
5     Two    10/30/2012  3        5         4       6
6     Three  10/29/2012  5        8         2      -1
7     Three  10/30/2012  5        6        -1       4
8     Four   10/29/2012  6        2        -1      -1
9     Four   10/30/2012  5        8        -1      -1

我想要的是返回以下内容:

1. 每个房间的 APhase 和 BPhase 的总和。
2. ACount 和 BCount 的值来自每个房间的最大日期
3. 如果 ACount 值为 -1，则使用前一个日期。与 BCount 相同。
4. 如果 ACount 值为 -1 并且前一个日期为 -1 等等。然后使用 0。与 BCount 相同。

我可以用这个查询得到数字 1 的查询

SELECT Room, sum(APhase) as TotalAPhase, sum(BPhase) as TotalBPhase 
FROM RoomTable 
WHERE Date between '10/28/2012' and '10/30/2012'
group by Room
order by Room

但我对如何包含数字 2-4 查询感到困惑。

这是我想要的输出

Room  TotalAPhase  TotalBPhase  ACount   BCount
One   10           13           7        6
Two   11           8            4        6
Three 10           13           2        4
Four  11           10           0        0

任何想法将不胜感激。谢谢。

Answer 1

希望这对你的情况有用:

SELECT 
Room
,SUM(APhase) AS TotalAPhase
,SUM(BPhase) AS TotalBPhase 
,ISNULL((    SELECT TOP 1 RT1.ACount 
             FROM RoomTable RT1 
             WHERE RT1.Room = RT.Room 
                AND RT1.ACount != -1
             ORDER BY RT1.Date DESC
), 0) AS ACount
,ISNULL((   SELECT TOP 1 RT2.BCount 
            FROM RoomTable RT2
            WHERE RT2.Room = RT.Room 
               AND RT2.BCount != -1
            ORDER BY RT2.Date DESC
), 0) AS BCount

FROM RoomTable RT
--WHERE Date between '10/28/2012' and '10/30/2012'
GROUP BY Room
ORDER BY Room

我不确定你是否真的需要那个 where 子句，所以我把它注释掉了。结果表中三号房间的 TotalBPhase 值应为 14，从 this SQL Fiddle demo 可以看出.