我有这个查询,当table上有匹配的行时,它做得很好Tiempo
。
问题在于,我真正需要查看的是中Tiempo
也存在的不匹配行Usuario
。
SELECT u.Id, t.empleado, CAST(MONTH(t.fecha) AS VARCHAR(2)) AS MonthNumber, CAST(YEAR(t.fecha) AS VARCHAR(4)) AS YearNumber, SUM(t.horas) AS Horas
FROM Usuario u
INNER JOIN Tiempo t ON u.username = t.empleado
WHERE fecha >= '2016-08-01' and fecha <= '2016-08-31'
GROUP BY u.id, t.empleado, CAST(MONTH(t.fecha) AS VARCHAR(2)), CAST(YEAR(t.fecha) AS VARCHAR(4))
这是输出:
Id empleado MonthNumber YearNumber Horas
86 username1 8 2016 96
95 username2 8 2016 80
99 username3 8 2016 47
102 username4 8 2016 85
这就是我要寻找的:
Id empleado MonthNumber YearNumber Horas
86 username1 8 2016 96
95 username2 8 2016 80
99 username3 8 2016 47
102 username4 8 2016 85
102 username5 8 2016 null (or 0)
编辑:样本数据:下载
假设表中存在缺少行的数据,则执行aLEFT JOIN
而不是INNER JOIN
:
SELECT u.Id,
COALESCE(t.empleado, 'NA')
COALESCE(CAST(MONTH(t.fecha) AS VARCHAR(2)), 'NA') AS MonthNumber,
COALESCE(CAST(YEAR(t.fecha) AS VARCHAR(4)), 'NA') AS YearNumber,
SUM(t.horas) AS Horas
FROM Usuario u
LEFT OUTER JOIN Tiempo t
ON u.username = t.empleado AND
fecha >= '2016-08-01' AND
fecha <= '2016-08-31'
GROUP BY u.id,
t.empleado,
CAST(MONTH(t.fecha) AS VARCHAR(2)),
CAST(YEAR(t.fecha) AS VARCHAR(4))
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句