SQL Server组通过每小时DateTime计数并更新Max计入表中?(SQL Server Group by Count of DateTime Per Hour and Update Max count into table?)
我有这段代码来计算每小时一组事件的发生次数。 现在我需要MAX COUNT并将其输入到不同的表中。 我是SQL新手,并且遇到与MAX结合的UPDATE语句和下面的所有问题。 任何人都可以帮忙吗? 谢谢!
SELECT CAST(locate_received_date as date) AS 'ForDate', DATEPART(hh, locate_received_date) AS 'OnHour', COUNT (*) AS 'Count' FROM BELL_DPRA2_locates_fact WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999' GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date);此脚本结果已附加。
编辑:这个答案为我工作,感谢@agfc。 我修改了一下为我工作。
UPDATE MyTable SET MaxHourlyCount = (SELECT Max(A.HourCount) FROM (SELECT CAST(locate_received_date as date) AS 'ForDate', DATEPART(hh, locate_received_date) AS 'OnHour', COUNT (*) AS HourCount FROM BELL_DPRA2_locates_fact WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999' GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date)) AS A)I have this piece of code that counts the number of occurrences for a set of events on an hourly basis. Now I need the MAX COUNT and input that in a different table. I am new to SQL and I am having problems with the UPDATE statement coupled with MAX and all that is below. Can anyone help please? Thanks!
SELECT CAST(locate_received_date as date) AS 'ForDate', DATEPART(hh, locate_received_date) AS 'OnHour', COUNT (*) AS 'Count' FROM BELL_DPRA2_locates_fact WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999' GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date);This script results is attached.
Edit: This answer worked for me thanks to @agfc. I modified a bit to work for me.
UPDATE MyTable SET MaxHourlyCount = (SELECT Max(A.HourCount) FROM (SELECT CAST(locate_received_date as date) AS 'ForDate', DATEPART(hh, locate_received_date) AS 'OnHour', COUNT (*) AS HourCount FROM BELL_DPRA2_locates_fact WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999' GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date)) AS A)最满意答案
-- I'd rename your 'Count' Field to something else not to use the SQL function name. UPDATE myOtherTable SET MaxField = SELECT Max(HourCount) FROM (SELECT CAST(locate_received_date as date) AS 'ForDate', DATEPART(hh, locate_received_date) AS 'OnHour', COUNT (*) AS HourCount FROM BELL_DPRA2_locates_fact WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999' GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date)) As MyMaxResult; -- I'd rename your 'Count' Field to something else not to use the SQL function name. UPDATE myOtherTable SET MaxField = SELECT Max(HourCount) FROM (SELECT CAST(locate_received_date as date) AS 'ForDate', DATEPART(hh, locate_received_date) AS 'OnHour', COUNT (*) AS HourCount FROM BELL_DPRA2_locates_fact WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999' GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date)) As MyMaxResult;更多推荐
发布评论