从其他表MSSQL中添加基于SELECT的新列

Question

我的Microsoft SQL数据库中有以下表格

监视器

名称fence_monitorbycicle_monitor[/tr>car_monitor

monitor_id	is_active
0	false
1	true
2	true

看起来您只需要连接表并使用条件聚合

SELECT
monitor_name  = m.name,
alltime       = ISNULL(SUM(count), 0),
today         = ISNULL(SUM(CASE WHEN time_stamp >= CAST(CAST(GETUTCDATE() AS date) AS datetime)
AND time_stamp < CAST(DATEADD(day, 1, CAST(GETUTCDATE() AS date)) AS datetime)
THEN count END), 0),
current_hour  = ISNULL(SUM(CASE WHEN time_stamp >= DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE()), '20000101')
AND time_stamp <  DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE()) + 1, '20000101')
THEN count END), 0)
FROM Monitors m
JOIN Logger l ON l.monitor = m.monitor_id
WHERE m.is_active = 1
GROUP BY
m.name;

db<gt；小提琴