如何按服务器和分钟获取订单列表（SQL 服务器）

你好-

我正在尝试获取每分钟通过每台服务器的订单列表。我可以通过一个简单的脚本来实现这一点，但我必须每分钟更改 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;