对我来说有难度的SQL

Question

date                       name
2010-12-01 15:20:12         a
2010-12-01 15:20:12         a
2010-12-03 15:20:12         b
2010-12-04 15:20:12         c
date                   count
2010-12-01               2
2010-12-02               0
2010-12-03               1
2010-12-04               1
2010-12-05               0
                 
要求 date为今年本月的日期,本月的天数要显示全，不能有遗漏。若某天没有数据用，其count为0；

Answer 1

关键在2010-12-02 0这个，否则group by就能搞定。可能你需要把你统计时间段的每一天虚拟一张表，然后left join一下。

Answer 2

# 给一个日期,得出当月所有日期列表  
#   
# declare @date datetime     
# set @date='2009-02-05'    
#     
# select [day]=convert(varchar,DATEADD(mm,DATEDIFF(mm,0,@date),0)+number,112)     
# from master..spt_values     
# where type='p'    
#   and number< datediff(dd,@date,dateadd(mm,1,@date))     先做一张日历表

Answer 3

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#Date') is null
drop table #Date
Go
Create table #Date([date] Datetime,[name] nvarchar(1))
Insert #Date
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-03 15:20:12',N'b' union all
select '2010-12-04 15:20:12',N'c'
GoIF OBJECT_ID('Tempdb..#1') IS NOT NULL
DROP TABLE #1
SELECT TOP 100 ID=IDENTITY(INT,0,1) INTO #1 FROM syscolumns a ,syscolumns b ,syscolumns cDECLARE @dt DATETIME
SET @dt='2010-12-01'
SELECT 
t1.Date,
COUNT(t2.[date]) AS [count]
FROM 
(SELECT  DATEADD(d,ID,@dt) AS Date FROM #1 AS a  WHERE DATEADD(d,a.ID,@dt)<'2011-01-01')AS t1
LEFT JOIN 
#Date AS t2 ON DATEDIFF(d,t1.Date,t2.[date])=0
GROUP BY t1.Date

Answer 4

SQL05以上版本可用use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#Date') is null
drop table #Date
Go
Create table #Date([date] Datetime,[name] nvarchar(1))
Insert #Date
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-03 15:20:12',N'b' union all
select '2010-12-04 15:20:12',N'c'
Go
DECLARE @dt DATETIME
SET @dt='2010-12-01'
;WITH Dt
AS
(SELECT @dt AS date 
UNION ALL
SELECT date+1 FROM dt WHERE date+1<'2011-01-01')
SELECT 
t1.Date,
COUNT(t2.[date]) AS [count] 
FROM 
Dt AS t1
LEFT JOIN 
#Date AS t2 ON DATEDIFF(d,t1.Date,t2.[date])=0
GROUP BY t1.Date
OPTION(MAXRECURSION 0)

Answer 5

create table tb(date datetime,name varchar(10))
insert into tb select '2010-12-01 15:20:12','a'
insert into tb select '2010-12-03 15:20:12','b'
insert into tb select '2010-12-04 15:20:12','c'
select a.date,b.name
from (
select dateadd(dd,number,convert(varchar(8),(select min(date) from tb),120)+'01') as date from master..spt_values where type='p'
) a left join tb b on a.date=convert(varchar(10),b.date,120) where a.date<=(select max(date) from tb)
go
drop table tb
/*
date                    name
----------------------- ----------
2010-12-01 00:00:00.000 a
2010-12-02 00:00:00.000 NULL
2010-12-03 00:00:00.000 b
2010-12-04 00:00:00.000 c(4 行受影响)*/

Answer 6

或,如要全月则:
create table tb(date datetime,name varchar(10))
insert into tb select '2010-12-01 15:20:12','a'
insert into tb select '2010-12-03 15:20:12','b'
insert into tb select '2010-12-04 15:20:12','c'
select a.date,b.name
from (
select dateadd(dd,number,convert(varchar(8),(select min(date) from tb),120)+'01') as date from master..spt_values where type='p'
) a left join tb b on a.date=convert(varchar(10),b.date,120) where a.date<(select dateadd(mm,1,convert(varchar(8),max(date),120)+'01') from tb)
go
drop table tb
/*
date                    name
----------------------- ----------
2010-12-01 00:00:00.000 a
2010-12-02 00:00:00.000 NULL
2010-12-03 00:00:00.000 b
2010-12-04 00:00:00.000 c
2010-12-05 00:00:00.000 NULL
2010-12-06 00:00:00.000 NULL
2010-12-07 00:00:00.000 NULL
2010-12-08 00:00:00.000 NULL
2010-12-09 00:00:00.000 NULL
2010-12-10 00:00:00.000 NULL
2010-12-11 00:00:00.000 NULL
2010-12-12 00:00:00.000 NULL
2010-12-13 00:00:00.000 NULL
2010-12-14 00:00:00.000 NULL
2010-12-15 00:00:00.000 NULL
2010-12-16 00:00:00.000 NULL
2010-12-17 00:00:00.000 NULL
2010-12-18 00:00:00.000 NULL
2010-12-19 00:00:00.000 NULL
2010-12-20 00:00:00.000 NULL
2010-12-21 00:00:00.000 NULL
2010-12-22 00:00:00.000 NULL
2010-12-23 00:00:00.000 NULL
2010-12-24 00:00:00.000 NULL
2010-12-25 00:00:00.000 NULL
2010-12-26 00:00:00.000 NULL
2010-12-27 00:00:00.000 NULL
2010-12-28 00:00:00.000 NULL
2010-12-29 00:00:00.000 NULL
2010-12-30 00:00:00.000 NULL
2010-12-31 00:00:00.000 NULL(31 行受影响)
*/

Answer 7

IF object_id('TB') IS NOT NULL DROP TABLE TB
Go
Create table TB([date] Datetime,[name] nvarchar(1))
Insert INTO TB
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-03 15:20:12',N'b' union all
select '2010-12-04 15:20:12',N'c'
Go
SELECT TOP(31) IDENTITY(INT,1,1) AS ID INTO #T FROM master..spt_valuesDECLARE  @YM VARCHAR(10)
SET @YM='2010-12-01'SELECT LEFT(@YM,8)+RIGHT('00'+LTRIM(ID),2) AS DATE,COUNT(date) AS COUNT FROM 
#T LEFT JOIN TB ON CAST(RIGHT(CONVERT(VARCHAR(10),date,120),2) AS INT)=ID
WHERE ID<=DAY(DATEADD(MM,1,@YM)-1)
GROUP BY ID

Answer 8

  
select day,isnull(counts,0)as counts from ( select [day]=convert(varchar,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)+number,23)     
  from master..spt_values     
  where type='p' and number< datediff(dd,GETDATE(),dateadd(mm,1,GETDATE()))) as 日历表 
 full join 
 (select convert(varchar,DateName,23) as DateStr,count(*) as counts from table4 group by Datename) as 表1 on 日历表.day=表1.DateStr引用2楼的日历表来实现才是正解，不用建临时表，明白人一看就明白。

Answer 9

Create table [Date]([date] Datetime,[name] nvarchar(1))
Insert [Date]
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-01 15:20:12',N'a' union all
select '2010-12-03 15:20:12',N'b' union all
select '2010-12-04 15:20:12',N'c'
Goselect m.[date] , isnull(n.[count],0) [count] from
(
select 
    dateadd(dd,num,convert(varchar(8),getdate(),120)+'01') [date]
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,convert(varchar(8),getdate(),120)+'01')<= dateadd(mm,1,convert(varchar(8),getdate(),120)+'01')-1
) m
left join
(
  select convert(varchar(10),[date],120) [date] , count(1) [count] from [date] group by convert(varchar(10),[date],120)
) n
on m.[date] = n.[date]
order by m.[date]drop table [Date]/*
date                                                   count       
------------------------------------------------------ ----------- 
2010-12-01 00:00:00.000                                2
2010-12-02 00:00:00.000                                0
2010-12-03 00:00:00.000                                1
2010-12-04 00:00:00.000                                1
2010-12-05 00:00:00.000                                0
2010-12-06 00:00:00.000                                0
2010-12-07 00:00:00.000                                0
2010-12-08 00:00:00.000                                0
2010-12-09 00:00:00.000                                0
2010-12-10 00:00:00.000                                0
2010-12-11 00:00:00.000                                0
2010-12-12 00:00:00.000                                0
2010-12-13 00:00:00.000                                0
2010-12-14 00:00:00.000                                0
2010-12-15 00:00:00.000                                0
2010-12-16 00:00:00.000                                0
2010-12-17 00:00:00.000                                0
2010-12-18 00:00:00.000                                0
2010-12-19 00:00:00.000                                0
2010-12-20 00:00:00.000                                0
2010-12-21 00:00:00.000                                0
2010-12-22 00:00:00.000                                0
2010-12-23 00:00:00.000                                0
2010-12-24 00:00:00.000                                0
2010-12-25 00:00:00.000                                0
2010-12-26 00:00:00.000                                0
2010-12-27 00:00:00.000                                0
2010-12-28 00:00:00.000                                0
2010-12-29 00:00:00.000                                0
2010-12-30 00:00:00.000                                0
2010-12-31 00:00:00.000                                0（所影响的行数为 31 行）*/