查询一天中每个小时每个接口的访问次数，怎么写

[fcy0306]

表结构：
id 接口名 开始访问时间 访问结束时间
1 jiekou1 2020-09-07 14:26:18.000000 2020-09-07 14:26:18.134646
2 jiekou2 *** ***
3 jiekou1 *** ***


要求的查询结果是
接口名 时间段 访问次数
1 1
2 0
3 1
jiekou1 · ·
· ·
· ·
23 2
24 0



1 1
2 0
3 1
jiekou2 · ·
· ·
· ·
23 2
24 0

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	id INT,
	接口名 NVARCHAR(10),
	开始访问时间 DATETIME
)
GO
INSERT INTO t VALUES (1,'jiekou1','2020-09-07 14:26:18')
INSERT INTO t VALUES (2,'jiekou1','2020-09-07 14:28:18')
INSERT INTO t VALUES (3,'jiekou2','2020-09-07 18:28:18')
GO
--以上为测试表及 测试数据----

SELECT t.接口名
	,sv.number AS 时间段
	,SUM(CASE WHEN DATEPART(hour,t.开始访问时间)+1=sv.number THEN 1 ELSE 0 END) AS 访问次数
FROM t OUTER apply [master].dbo.spt_values AS sv 
WHERE sv.type='P' AND sv.number BETWEEN 1 AND 24
GROUP BY t.[接口名],sv.number
ORDER BY t.[接口名],sv.number
/*
接口名	时间段	访问次数
jiekou1	1	0
jiekou1	2	0
jiekou1	3	0
jiekou1	4	0
jiekou1	5	0
jiekou1	6	0
jiekou1	7	0
jiekou1	8	0
jiekou1	9	0
jiekou1	10	0
jiekou1	11	0
jiekou1	12	0
jiekou1	13	0
jiekou1	14	0
jiekou1	15	2
jiekou1	16	0
jiekou1	17	0
jiekou1	18	0
jiekou1	19	0
jiekou1	20	0
jiekou1	21	0
jiekou1	22	0
jiekou1	23	0
jiekou1	24	0
jiekou2	1	0
jiekou2	2	0
jiekou2	3	0
jiekou2	4	0
jiekou2	5	0
jiekou2	6	0
jiekou2	7	0
jiekou2	8	0
jiekou2	9	0
jiekou2	10	0
jiekou2	11	0
jiekou2	12	0
jiekou2	13	0
jiekou2	14	0
jiekou2	15	0
jiekou2	16	0
jiekou2	17	0
jiekou2	18	0
jiekou2	19	1
jiekou2	20	0
jiekou2	21	0
jiekou2	22	0
jiekou2	23	0
jiekou2	24	0
*/

[fcy0306]

引用 1 楼 吉普赛的歌 的回复:

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	id INT,
	接口名 NVARCHAR(10),
	开始访问时间 DATETIME
)
GO
INSERT INTO t VALUES (1,'jiekou1','2020-09-07 14:26:18')
INSERT INTO t VALUES (2,'jiekou1','2020-09-07 14:28:18')
INSERT INTO t VALUES (3,'jiekou2','2020-09-07 18:28:18')
GO
--以上为测试表及 测试数据----

SELECT t.接口名
	,sv.number AS 时间段
	,SUM(CASE WHEN DATEPART(hour,t.开始访问时间)+1=sv.number THEN 1 ELSE 0 END) AS 访问次数
FROM t OUTER apply [master].dbo.spt_values AS sv 
WHERE sv.type='P' AND sv.number BETWEEN 1 AND 24
GROUP BY t.[接口名],sv.number
ORDER BY t.[接口名],sv.number
/*
接口名	时间段	访问次数
jiekou1	1	0
jiekou1	2	0
jiekou1	3	0
jiekou1	4	0
jiekou1	5	0
jiekou1	6	0
jiekou1	7	0
jiekou1	8	0
jiekou1	9	0
jiekou1	10	0
jiekou1	11	0
jiekou1	12	0
jiekou1	13	0
jiekou1	14	0
jiekou1	15	2
jiekou1	16	0
jiekou1	17	0
jiekou1	18	0
jiekou1	19	0
jiekou1	20	0
jiekou1	21	0
jiekou1	22	0
jiekou1	23	0
jiekou1	24	0
jiekou2	1	0
jiekou2	2	0
jiekou2	3	0
jiekou2	4	0
jiekou2	5	0
jiekou2	6	0
jiekou2	7	0
jiekou2	8	0
jiekou2	9	0
jiekou2	10	0
jiekou2	11	0
jiekou2	12	0
jiekou2	13	0
jiekou2	14	0
jiekou2	15	0
jiekou2	16	0
jiekou2	17	0
jiekou2	18	0
jiekou2	19	1
jiekou2	20	0
jiekou2	21	0
jiekou2	22	0
jiekou2	23	0
jiekou2	24	0
*/

大佬好，我用你的语句控制台说ERROR: syntax error at or near "OUTER" OUTER附近语法错误，我用的是postgresql数据库

[fcy0306]

postsql数据库解法：

select project_name,to_char(gmt_create,'hh24')as hourly,count(project_name)
from log_info
WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
group by project_name,to_char(gmt_create,'hh24')
having count(project_name)>=0

如果某时间段没数据则不会显示