我有两个查询，可得到两组结果：
时间查询：
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
空白可以是NULL或0。
您似乎想要：
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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句