[ad_1]
你好-
我正在尝试获取每分钟通过每台服务器的订单列表。 我可以通过一个简单的脚本来实现这一点,但我必须每分钟更改 WHERE 子句。 我希望找到可以在一个查询中查询范围的东西。
我希望能得到这样的结果……
SERVER 9:40 9:41 9:42 9:43 9:44 9:45 SV1 9 12 7 8 1 2 SV2 16 10 8 11 18 25 SV3 27 22 29 11 4 7 SV4 22 20 22 22 47 47
谢谢 !!!
我尝试过的:
这是有效的查询,但我每次都必须更改分钟。 我必须将 09:40 更改为 09:41,然后更改为 09:42,依此类推。
SQL
SELECT h.line AS 'SERVER' ,COUNT(DISTINCT c.id) AS '9:40' FROM customer c INNER JOIN orders o ON c.id = o.id INNER JOIN handle h ON c.line = h.line WHERE c.datetime BETWEEN '2024-02-21 09:40:00.000' AND '2024-02-21 09:40:59.999' GROUP BY h.line ORDER BY 1
我已经尝试过这个,但它需要非常长的时间,如果我想查询不同的范围,我仍然必须更改分钟。
SQL
SELECT h.line AS 'SERVER' ,(SELECT COUNT(DISTINCT c1.id) FROM customer c1 INNER JOIN orders o1 ON c1.id = o1.id INNER JOIN handle h1 ON c1.line = h1.line WHERE h.line = h1.line AND c1.datetime BETWEEN '2024-02-21 09:40:00.000' AND '2024-02-21 09:40:59.999') AS '9:40' ,(SELECT COUNT(DISTINCT c2.id) FROM customer c2 INNER JOIN orders o2 ON c2.id = o2.id INNER JOIN handle h2 ON c2.line = h2.line WHERE h.line = h2.line AND c2.datetime BETWEEN '2024-02-21 09:41:00.000' AND '2024-02-21 09:41:59.999') AS '9:41' ,(SELECT COUNT(DISTINCT c3.id) FROM customer c3 INNER JOIN orders o3 ON c3.id = o3.id INNER JOIN handle h3 ON c3.line = h3.line WHERE h.line = h3.line AND c3.datetime BETWEEN '2024-02-21 09:42:00.000' AND '2024-02-21 09:42:59.999') AS '9:42' ,(SELECT COUNT(DISTINCT c4.id) FROM customer c4 INNER JOIN orders o4 ON c4.id = o4.id INNER JOIN handle h4 ON c4.line = h4.line WHERE h.line = h4.line AND c4.datetime BETWEEN '2024-02-21 09:43:00.000' AND '2024-02-21 09:43:59.999') AS '9:43' ,(SELECT COUNT(DISTINCT c5.id) FROM customer c5 INNER JOIN orders o5 ON c5.id = o5.id INNER JOIN handle h5 ON c5.line = h5.line WHERE h.line = h5.line AND c5.datetime BETWEEN '2024-02-21 09:44:00.000' AND '2024-02-21 09:44:59.999') AS '9:44' ,(SELECT COUNT(DISTINCT c6.id) FROM customer c6 INNER JOIN orders o6 ON c6.id = o6.id INNER JOIN handle h6 ON c6.line = h6.line WHERE h.line = h6.line AND c6.datetime BETWEEN '2024-02-21 09:45:00.000' AND '2024-02-21 09:45:59.999') AS '9:45' FROM customer c INNER JOIN orders o ON c.id = o.id INNER JOIN handle h ON c.line = h.line GROUP BY h.line ORDER BY 1
解决方案1
首先计算每分钟每个服务器的订单数:
SQL
DECLARE @day datetime = '2024-02-21'; SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id INNER JOIN handle h ON h.line = c.line CROSS APPLY (SELECT FORMAT(c.datetime, 'HH:mm')) As M (Minute) WHERE c.datetime >= @day And c.datetime < DateAdd(day, 1, @day) GROUP BY c.line, M.Minute ORDER BY c.line, M.Minute ;
如果您确实想要每分钟一列,那么您需要使用 PIVOT
– 这仍然需要您为每分钟添加一个条目:
使用 PIVOT 和 UNPIVOT – SQL Server | 微软学习[^]
SQL
DECLARE @day datetime = '2024-02-21'; WITH cteSource As ( SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id INNER JOIN handle h ON h.line = c.line CROSS APPLY (SELECT FORMAT(c.datetime, 'HH:mm')) As M (Minute) WHERE c.datetime >= @day And c.datetime < DateAdd(day, 1, @day) GROUP BY c.line, M.Minute ) SELECT SERVER, [09:40], [09:41], [09:42], [09:43], [09:44], [09:45] FROM cteSource As S PIVOT ( SUM(OrderCount) FOR Minute In ([09:40], [09:41], [09:42], [09:43], [09:44], [09:45]) ) As P ORDER BY SERVER ;
如果您想避免输入所有分钟,那么您将需要一个动态枢轴 – 例如:
SQL
DECLARE @day datetime = '2024-02-21'; DECLARE @columns nvarchar(max); DECLARE @sql nvarchar(max); WITH cteHours As ( SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As H FROM sys.all_columns ), cteMinutes As ( SELECT TOP 60 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As M FROM sys.all_columns ) SELECT @columns = STUFF( (SELECT N', [' + FORMAT(H.H, '00') + N':' + FORMAT(M.M, '00') + N']' FROM cteHours H CROSS APPLY cteMinutes M FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, N'') ; SET @sql = N' WITH cteSource As ( SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id INNER JOIN handle h ON h.line = c.line CROSS APPLY (SELECT FORMAT(c.datetime, ''HH:mm'')) As M (Minute) WHERE c.datetime >= @day And c.datetime < DateAdd(day, 1, @day) GROUP BY c.line, M.Minute ) SELECT SERVER, ' + @columns + N' FROM cteSource As S PIVOT ( SUM(OrderCount) FOR Minute In (' + @columns + N') ) As P ORDER BY SERVER ;'; EXEC sp_executesql @sql, N'@day datetime', @day = @day;
[ad_2]
コメント