急~汇总SQL 在线等

[jiangnan601]

表数据：
ID CreateDate Name
1 2019-10-16
2 2019-10-14
3 2019-10-10
4 2019-10-16
5 2019-10-01
6 2019-10-05
7 2019-10-08

要求SQL统计出十月份每一天的注册数量，结果如下

CreateDate Count
2019-10-01 1
2019-10-02 0
2019-10-03 0
2019-10-04 0
2019-10-05 1
2019-10-06 0
......（07~15中间省略了..）
2019-10-16 1
2019-10-30 0

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[CreateDate] Date)
Insert #T
select 1,'2019-10-16' union all
select 2,'2019-10-14' union all
select 3,'2019-10-10' union all
select 4,'2019-10-16' union all
select 5,'2019-10-01' union all
select 6,'2019-10-05' union all
select 7,'2019-10-08'
Go
--测试数据结束
SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) [DataTime],
       ISNULL(Count,0) Count
FROM master..spt_values
    LEFT JOIN (SELECT CreateDate,COUNT(1) AS Count FROM #T GROUP BY CreateDate)t
        ON DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) = [CreateDate]
WHERE type = 'P'
      AND number
      BETWEEN 0 AND DATEDIFF(DAY, '2019-10-01', DATEADD(MONTH, 1, '2019-10-01'))-1;

[stelf]

顶楼上，先构造出一个日历类的序列数据，然后再和你的业务数据做关联

[jiangnan601]

引用 1 楼 二月十六 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[CreateDate] Date)
Insert #T
select 1,'2019-10-16' union all
select 2,'2019-10-14' union all
select 3,'2019-10-10' union all
select 4,'2019-10-16' union all
select 5,'2019-10-01' union all
select 6,'2019-10-05' union all
select 7,'2019-10-08'
Go
--测试数据结束
SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) [DataTime],
       ISNULL(Count,0) Count
FROM master..spt_values
    LEFT JOIN (SELECT CreateDate,COUNT(1) AS Count FROM #T GROUP BY CreateDate)t
        ON DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) = [CreateDate]
WHERE type = 'P'
      AND number
      BETWEEN 0 AND DATEDIFF(DAY, '2019-10-01', DATEADD(MONTH, 1, '2019-10-01'))-1;

你这个时间加上时分秒就不行了

[二月十六]

引用 3 楼 jiangnan601 的回复：

[quote=引用 1 楼 二月十六 的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[CreateDate] Date)
Insert #T
select 1,'2019-10-16' union all
select 2,'2019-10-14' union all
select 3,'2019-10-10' union all
select 4,'2019-10-16' union all
select 5,'2019-10-01' union all
select 6,'2019-10-05' union all
select 7,'2019-10-08'
Go
--测试数据结束
SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) [DataTime],
       ISNULL(Count,0) Count
FROM master..spt_values
    LEFT JOIN (SELECT CreateDate,COUNT(1) AS Count FROM #T GROUP BY CreateDate)t
        ON DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) = [CreateDate]
WHERE type = 'P'
      AND number
      BETWEEN 0 AND DATEDIFF(DAY, '2019-10-01', DATEADD(MONTH, 1, '2019-10-01'))-1;

你这个时间加上时分秒就不行了[/quote]你的问题里是没有分秒的，如果你给的数据不对，时间是有分秒的，把时间转换一下，只取日期即可。

[jiangnan601]

要加上时间

引用 楼主 jiangnan601 的回复:

表数据：
ID CreateDate Name
1 2019-10-16 08:00
2 2019-10-14 07:22
3 2019-10-10 07:21
4 2019-10-16 07:30
5 2019-10-01 07:31
6 2019-10-05 07:32
7 2019-10-08 07:33

要求SQL统计出十月份每一天的注册数量，结果如下

CreateDate Count
2019-10-01 1
2019-10-02 0
2019-10-03 0
2019-10-04 0
2019-10-05 1
2019-10-06 0
......（07~15中间省略了..）
2019-10-16 1
2019-10-30 0

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[CreateDate] DATETIME)
Insert #T
select 1,'2019-10-16 10:10:10' union all
select 2,'2019-10-14' union all
select 3,'2019-10-10' union all
select 4,'2019-10-16 11:10:10' union all
select 5,'2019-10-01' union all
select 6,'2019-10-05' union all
select 7,'2019-10-08'
Go
--测试数据结束
SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) [DataTime],
       ISNULL(Count,0) Count
FROM master..spt_values
    LEFT JOIN (SELECT CONVERT(DATE,CreateDate) AS CreateDate,COUNT(1) AS Count FROM #T GROUP BY CONVERT(DATE,CreateDate))t
        ON DATEADD(DAY, number, CONVERT(DATETIME, '2019-10-01')) = [CreateDate]
WHERE type = 'P'
      AND number
      BETWEEN 0 AND DATEDIFF(DAY, '2019-10-01', DATEADD(MONTH, 1, '2019-10-01'))-1;