SQL Server连接两个表,并保留自己的值和日期

loading...


1

我有两个查询,它给我两组结果:

时间查询:

SELECT a.AID AID, CONCAT(CONVERT(date, c.CDate),DATEPART(HOUR, c.CDate)) CDateHour, SUM(c.time/60/1000) duration
FROM TableA a
INNER JOIN TableB b ON b.bkey = a.akey
LEFT JOIN TableC c ON b.subbkey = c.subckey
WHERE a.AID = '10000'
GROUP BY c.CDate, a.AID
ORDER BY c.CDate;

时间结果:

AID     CDateHour    duration
10000   2018-10-259  91
10000   2018-10-2512 25
10000   2018-10-2513 25
10000   2018-10-2514 14
10000   2018-10-2515 20

计数查询:

SELECT a.AID AID, CONCAT(CONVERT(date, d.DDate),DATEPART(HOUR, d.DDate)) DDateHour, COUNT(DISTINCT e.EID) COUNTEID
FROM TableA a
INNER JOIN TableD d ON d.dto = a.AID
INNER JOIN TableE e ON e.ekey = d.dkey
WHERE a.AID = '10000'
GROUP BY d.DDate, a.AID
ORDER BY d.DDate

数结果:

AID     DDateHour    COUNTEID
10000   2018-10-256  2
10000   2018-10-257  28
10000   2018-10-258  44
10000   2018-10-259  22
10000   2018-10-2510 49
10000   2018-10-2511 46
10000   2018-10-2512 37

我想加入这两个查询以获得以下输出:

AID     DateHour      duration  COUNTID
10000   2018-10-256             2
10000   2018-10-257             28
10000   2018-10-258             44   
10000   2018-10-259   91        22
10000   2018-10-2510            49
10000   2018-10-2511            46
10000   2018-10-2512  25        37
10000   2018-10-2513  25
10000   2018-10-2514  14
10000   2018-10-2515  20

空白可以是 NULL0 .

1回答

  • 4

    你似乎想要:

    with q1 as (<first query here without order by>),
         q2 as (<second query without order by>)
    select coalesce(q1.AID, q2.AID) as AID,
           coalesce(q1.DDateHour, q2.DDateHour) as DDateHour,
           q1.duration, q2.counteid
    from q1 full join
         q2
         on q1.AID = q2.AID and
            q1.DDateHour = q2.DDateHour;
    
评论

loading...

暂时没有评论!