34,838
社区成员




USE tempdb
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
[Time] DATETIME
)
GO
INSERT INTO t VALUES ('2020-09-01 23:15:00')
INSERT INTO t VALUES ('2020-09-02 22:15:00')
INSERT INTO t VALUES ('2020-09-02 23:15:00')
GO
--------- 以上为测试表及测试数据
--1. 增加计算列
ALTER TABLE t ADD yyyyMMdd AS CONVERT(CHAR(10),[Time],120) PERSISTED;
GO
--2. 增加索引
CREATE INDEX IX_t_yyyyMMdd ON t(yyyyMMdd);
GO
--3. 汇总查询
SELECT yyyyMMdd,COUNT(1) AS cnt FROM t GROUP BY yyyyMMdd