使用具有GROUP BY CASE

Question

我在T-SQL下面的查询：

SELECT dbo.table2.device, 
    dbo.table2.CREATETIME AS create_time, 
    CASE WHEN dbo.table1.ACTIONID = 1 
     THEN dbo.table1.startstop 
    END AS start_time, 
    CASE WHEN dbo.table1.ACTIONID = 2 
     THEN dbo.table1.startstop 
    END AS stop_time, 
    dbo.table2.collect_time 
FROM dbo.table2 
JOIN dbo.table1 ON dbo.table1.CREATETIME = dbo.table2.CREATETIME; 

......这给了我一个结果表与几行，每重复一次用的开始 - 一旦最终时间（SQL时间 - 缩短为简单起见） - 另一个是NULL - 如：

device | create_time | start_time | stop_time | collect_time 
1  | 0000001  | 0000001 | NULL  | 0000001 
1  | 0000001  | NULL  | 0000002 | 0000001 

我希望将这两行（与CREATE_TIME为ID），所以我让他们进入一个....谢谢！

Answer 1

可以聚集（SUM）这些列：

SELECT dbo.table2.device, 
    dbo.table2.CREATETIME AS create_time, 
    SUM(CASE WHEN dbo.table1.ACTIONID = 1 
     THEN dbo.table1.startstop ELSE 0 
    END) AS start_time, 
    SUM(CASE WHEN dbo.table1.ACTIONID = 2 
     THEN dbo.table1.startstop ELSE 0 
    END) AS stop_time, 
    dbo.table2.collect_time 
FROM dbo.table2 
JOIN dbo.table1 ON dbo.table1.CREATETIME = dbo.table2.CREATETIME 
GROUP BY dbo.table2.device, dbo.table2.CREATETIME, dbo.table2.collect_time; 

Answer 2

我也想使用子查询将制定以及

SELECT 
X.Device 
,sum(X.start_time) 
,sum(X.stop_time) 
,X.collect_time 
from(
SELECT dbo.table2.device, 
    dbo.table2.CREATETIME AS create_time, 
    CASE WHEN dbo.table1.ACTIONID = 1 
     THEN dbo.table1.startstop 
    END AS start_time, 
    CASE WHEN dbo.table1.ACTIONID = 2 
     THEN dbo.table1.startstop 
    END AS stop_time, 
    dbo.table2.collect_time 
FROM dbo.table2 
JOIN dbo.table1 ON dbo.table1.CREATETIME = dbo.table2.CREATETIME) AS X 
group by 
X.Device, X.collect_time