SELECT DateTimePerHour,MonitorID,SubTotal / StatusCountSum AS 'AverageValue',Status
FROM (
SELECT DateTimePerHour,MonitorID,Status,
SUM(StatusCount) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'StatusCountSum',
SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY StatusCount DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status,COUNT(*) AS 'StatusCount',SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status
) AS K
) AS Q
WHERE Q.NO = 1
恩?你是要撈最大的筆數資料吧?
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c9cbc8f5eacda8e924b1999276a3eebd
select DateTimePerHour
,MonitorID
,Status
,StatusCount
from (
Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
,MonitorID
,Status
,count(*) StatusCount
,Row_Number()over(
PARTITION BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
,MonitorID
order by count(*) desc
) Sort
From tmp
group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
,Status
,MonitorID
) k
where Sort = 1
是這樣沒錯,但是最後的結果要放到表B
照你推估應該會這樣~不過秒數有篩掉一些
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9c9ca3d5dc9888459cbd9e29c0b27981
select DateTimePerHour
,MonitorID
,AverageValue
,Status
from (
Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
,MonitorID
,Status
,AVG(Value) AverageValue
,count(*) StatusCount
,Row_Number()over(
PARTITION BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
,MonitorID
order by count(*) desc
) Sort
From tmp
group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
,Status
,MonitorID
) k
where Sort = 1
感謝您的回答,不過rogeryao的解答是我需要的結果,最佳解就給他了