根据某大神给我的回复改了改 大神 眼熟否~create table test (id int,[uid] int,[date] datetime) insert test select 1,1,'2014-01-01' union all select 2,2,'2014-01-01' union all select 3,1,'2014-01-01' union all select 4,1,'2014-01-03' union all select 5,2,'2014-01-04' union all select 6,1,'2014-01-04' union all select 7,1,'2014-01-06' union all select 8,2,'2014-01-06' union all select 9,1,'2014-01-07'DECLARE @start datetime = '2014-01-01' DECLARE @end datetime = '2014-01-07' DECLARE @sint int DECLARE @eint int set @sint=DATEPART(day,@start) set @eint=DATEPART(day,@start)+DATEdiff(DAY,@start,@end) set language N'Simplified Chinese';WITH TT AS ( SELECT number FROM master..spt_values WHERE type = 'P' AND number BETWEEN @sint-1 AND @eint-1 ) select b.[date], [count]=isnull(a.[count],0), 星期=datename(WEEKDAY, b.[date])from ( select [date], [count]=COUNT(*) from test group by [date] ) a right join ( select [date]=DATEADD(DAY,TT.number,@start) from TT ) b ON a.[date]=b.[date] /* 2014-01-01 00:00:00.000 3 星期三 2014-01-02 00:00:00.000 0 星期四 2014-01-03 00:00:00.000 1 星期五 2014-01-04 00:00:00.000 2 星期六 2014-01-05 00:00:00.000 0 星期日 2014-01-06 00:00:00.000 2 星期一 2014-01-07 00:00:00.000 1 星期二 */
上面为参数赋值赋错了~ 改这个 而且只是用与同一年月 要是跨年、跨月 会报错 跨年、跨月 需要弄一个保存所有日期的表 麻烦 DECLARE @start datetime = '2014-01-01' DECLARE @end datetime = '2014-01-07' DECLARE @sint int DECLARE @eint int set @sint=DATEPART(day,@start) set @eint=DATEdiff(DAY,@start,@end) set language N'Simplified Chinese';WITH TT AS ( SELECT number FROM master..spt_values WHERE type = 'P' AND number BETWEEN @sint-1 AND @eint ) select b.[date], [count]=isnull(a.[count],0), 星期=datename(WEEKDAY, b.[date])from ( select [date], [count]=COUNT(*) from test group by [date] ) a right join ( select [date]=DATEADD(DAY,TT.number,@start) from TT ) b ON a.[date]=b.[date]
试试DECLARE @start datetime DECLARE @end datetime DECLARE @sint int DECLARE @eint int set @start='2014-01-01' set @end='2014-01-07' set @sint=DATEPART(day,@start) set @eint=DATEPART(day,@start)+DATEdiff(DAY,@start,@end) set language N'Simplified Chinese'
create table test (id int,[uid] int,[date] datetime) insert test select 1,1,'2014-01-01' union all select 2,2,'2014-01-01' union all select 3,1,'2014-01-01' union all select 4,1,'2014-01-03' union all select 5,2,'2014-01-04' union all select 6,1,'2014-01-04' union all select 7,1,'2014-01-06' union all select 8,2,'2014-01-06' union all select 9,1,'2014-01-07' DECLARE @start datetime DECLARE @end datetime DECLARE @sint int DECLARE @eint int set @start='2014-01-01' set @end='2014-01-07' set @sint=DATEPART(day,@start) set @eint=DATEdiff(DAY,@start,@end) set language N'Simplified Chinese'
;WITH TT AS ( SELECT number FROM master..spt_values WHERE type = 'P' AND number BETWEEN @sint-1 AND @eint ) select b.[date], [count]=isnull(a.[count],0), 星期=datename(WEEKDAY, b.[date])
from ( select [date], [count]=COUNT(*) from test group by [date] ) a right join ( select [date]=DATEADD(DAY,TT.number,@start) from TT ) b ON a.[date]=b.[date]
--sql2000if object_id('test') is not null drop table test create table test (id int,[uid] int,[date] datetime) insert test select 1,1,'2014-01-01' union all select 2,2,'2014-01-01' union all select 3,1,'2014-01-01' union all select 4,1,'2014-01-03' union all select 5,2,'2014-01-04' union all select 6,1,'2014-01-04' union all select 7,1,'2014-01-06' union all select 8,2,'2014-01-06' union all select 9,1,'2014-01-07'
declare @t1 table (tdate datetime) declare @i int declare @j int declare @startdate datetime
select @j=datediff(d,min([date]),max([date])),@startdate=min([date]) from testset @i = 0while @i <= @jbegininsert into @t1values(dateadd(d,@i,@startdate))set @i = @i + 1endselect a.tdate as [date],datename(dw,a.tdate) as [datename],count(b.[date]) as [count] from @t1 a left join test b on a.tdate=b.[date]group by a.tdate 2014-01-01 00:00:00.000 星期三 3 2014-01-02 00:00:00.000 星期四 0 2014-01-03 00:00:00.000 星期五 1 2014-01-04 00:00:00.000 星期六 2 2014-01-05 00:00:00.000 星期日 0 2014-01-06 00:00:00.000 星期一 2 2014-01-07 00:00:00.000 星期二 1
先生成一个 单列但日期无缺的表 然后关联group的结果
这个是可以根据输入起始时间和结束时间,然后将其按天划分为一天天 -- 查询时间段按日划分表 IF object_id('tempdb..#tempDate') is not null BEGIN drop table #tempDate END CREATE table #tempDate ( stat_day varchar(10) ) CREATE clustered index tempDate_Index1 on #tempDate (stat_day)
declare @time_temp datetime set @time_temp = @time_start while @time_temp < @time_end begin insert into #tempDate (stat_day) values (CONVERT(varchar(10),@time_temp,121)) set @time_temp= dateadd(d,1,@time_temp) end 将该临时表和你的表进行左关联分组统计得到你要的记录至于第2个结果集,就是将日期转为星期几就可以了
把楼上几位大神的结果整理了下。。create table #test (id int,[uid] int,[date] datetime) insert #test select 1,1,'2014-01-01' union all select 2,2,'2014-01-01' union all select 3,1,'2014-01-01' union all select 4,1,'2014-01-03' union all select 5,2,'2014-01-04' union all select 6,1,'2014-01-04' union all select 7,1,'2014-01-06' union all select 8,2,'2014-01-06' union all select 9,1,'2014-01-07' CREATE table #tempDate ( stat_day varchar(10) ) CREATE clustered index tempDate_Index1 on #tempDate (stat_day)
declare @time_temp datetime set @time_temp = '2014-01-01' while @time_temp < '2014-01-07' begin insert into #tempDate (stat_day) values (CONVERT(varchar(10),@time_temp,121)) set @time_temp= dateadd(d,1,@time_temp) endSelect Count(#test.[date]), #tempDate.stat_day From #tempDate Left Outer Join #test On #tempDate.stat_day = #test.[date] Group By #tempDate.stat_day
Select Count(#test.[date]), datename(WEEKDAY, #tempDate.stat_day) From #tempDate Left Outer Join #test On #tempDate.stat_day = #test.[date] Group By #tempDate.stat_dayIF object_id('tempdb..#test') is not null BEGIN drop table #test ENDIF object_id('tempdb..#tempDate') is not null BEGIN drop table #tempDate END
大神 眼熟否~create table test (id int,[uid] int,[date] datetime)
insert test
select 1,1,'2014-01-01' union all
select 2,2,'2014-01-01' union all
select 3,1,'2014-01-01' union all
select 4,1,'2014-01-03' union all
select 5,2,'2014-01-04' union all
select 6,1,'2014-01-04' union all
select 7,1,'2014-01-06' union all
select 8,2,'2014-01-06' union all
select 9,1,'2014-01-07'DECLARE @start datetime = '2014-01-01'
DECLARE @end datetime = '2014-01-07'
DECLARE @sint int
DECLARE @eint int
set @sint=DATEPART(day,@start)
set @eint=DATEPART(day,@start)+DATEdiff(DAY,@start,@end)
set language N'Simplified Chinese';WITH TT AS (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN @sint-1 AND @eint-1
)
select b.[date],
[count]=isnull(a.[count],0),
星期=datename(WEEKDAY, b.[date])from (
select [date],
[count]=COUNT(*)
from test
group by [date]
) a
right join (
select [date]=DATEADD(DAY,TT.number,@start)
from TT
) b
ON a.[date]=b.[date]
/*
2014-01-01 00:00:00.000 3 星期三
2014-01-02 00:00:00.000 0 星期四
2014-01-03 00:00:00.000 1 星期五
2014-01-04 00:00:00.000 2 星期六
2014-01-05 00:00:00.000 0 星期日
2014-01-06 00:00:00.000 2 星期一
2014-01-07 00:00:00.000 1 星期二
*/
而且只是用与同一年月 要是跨年、跨月 会报错
跨年、跨月 需要弄一个保存所有日期的表 麻烦
DECLARE @start datetime = '2014-01-01'
DECLARE @end datetime = '2014-01-07'
DECLARE @sint int
DECLARE @eint int
set @sint=DATEPART(day,@start)
set @eint=DATEdiff(DAY,@start,@end)
set language N'Simplified Chinese';WITH TT AS (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN @sint-1 AND @eint
)
select b.[date],
[count]=isnull(a.[count],0),
星期=datename(WEEKDAY, b.[date])from (
select [date],
[count]=COUNT(*)
from test
group by [date]
) a
right join (
select [date]=DATEADD(DAY,TT.number,@start)
from TT
) b
ON a.[date]=b.[date]
消息 139,级别 15,状态 1,第 0 行
不能向局部变量赋予默认值。
消息 139,级别 15,状态 1,第 0 行
不能向局部变量赋予默认值。
消息 137,级别 15,状态 2,第 5 行
必须声明标量变量 "@start"。
消息 137,级别 15,状态 2,第 6 行
必须声明标量变量 "@start"。
消息 137,级别 15,状态 2,第 26 行
必须声明标量变量 "@start"。
DECLARE @end datetime
DECLARE @sint int
DECLARE @eint int
set @start='2014-01-01'
set @end='2014-01-07'
set @sint=DATEPART(day,@start)
set @eint=DATEPART(day,@start)+DATEdiff(DAY,@start,@end)
set language N'Simplified Chinese'
create table test (id int,[uid] int,[date] datetime)
insert test
select 1,1,'2014-01-01' union all
select 2,2,'2014-01-01' union all
select 3,1,'2014-01-01' union all
select 4,1,'2014-01-03' union all
select 5,2,'2014-01-04' union all
select 6,1,'2014-01-04' union all
select 7,1,'2014-01-06' union all
select 8,2,'2014-01-06' union all
select 9,1,'2014-01-07'
DECLARE @start datetime
DECLARE @end datetime
DECLARE @sint int
DECLARE @eint int
set @start='2014-01-01'
set @end='2014-01-07'
set @sint=DATEPART(day,@start)
set @eint=DATEdiff(DAY,@start,@end)
set language N'Simplified Chinese'
;WITH TT AS (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN @sint-1 AND @eint
)
select b.[date],
[count]=isnull(a.[count],0),
星期=datename(WEEKDAY, b.[date])
from (
select [date],
[count]=COUNT(*)
from test
group by [date]
) a
right join (
select [date]=DATEADD(DAY,TT.number,@start)
from TT
) b
ON a.[date]=b.[date]
drop table test
create table test (id int,[uid] int,[date] datetime)
insert test select 1,1,'2014-01-01' union all
select 2,2,'2014-01-01' union all
select 3,1,'2014-01-01' union all
select 4,1,'2014-01-03' union all
select 5,2,'2014-01-04' union all
select 6,1,'2014-01-04' union all
select 7,1,'2014-01-06' union all
select 8,2,'2014-01-06' union all
select 9,1,'2014-01-07'
declare @t1 table (tdate datetime)
declare @i int
declare @j int
declare @startdate datetime
select @j=datediff(d,min([date]),max([date])),@startdate=min([date]) from testset @i = 0while @i <= @jbegininsert into @t1values(dateadd(d,@i,@startdate))set @i = @i + 1endselect a.tdate as [date],datename(dw,a.tdate) as [datename],count(b.[date]) as [count] from @t1 a left join test b on a.tdate=b.[date]group by a.tdate 2014-01-01 00:00:00.000 星期三 3
2014-01-02 00:00:00.000 星期四 0
2014-01-03 00:00:00.000 星期五 1
2014-01-04 00:00:00.000 星期六 2
2014-01-05 00:00:00.000 星期日 0
2014-01-06 00:00:00.000 星期一 2
2014-01-07 00:00:00.000 星期二 1
然后关联group的结果
IF object_id('tempdb..#tempDate') is not null
BEGIN
drop table #tempDate
END
CREATE table #tempDate
(
stat_day varchar(10)
)
CREATE clustered index tempDate_Index1 on #tempDate (stat_day)
declare @time_temp datetime
set @time_temp = @time_start
while @time_temp < @time_end
begin
insert into #tempDate (stat_day) values (CONVERT(varchar(10),@time_temp,121))
set @time_temp= dateadd(d,1,@time_temp)
end
将该临时表和你的表进行左关联分组统计得到你要的记录至于第2个结果集,就是将日期转为星期几就可以了
insert #test
select 1,1,'2014-01-01' union all
select 2,2,'2014-01-01' union all
select 3,1,'2014-01-01' union all
select 4,1,'2014-01-03' union all
select 5,2,'2014-01-04' union all
select 6,1,'2014-01-04' union all
select 7,1,'2014-01-06' union all
select 8,2,'2014-01-06' union all
select 9,1,'2014-01-07' CREATE table #tempDate
(
stat_day varchar(10)
)
CREATE clustered index tempDate_Index1 on #tempDate (stat_day)
declare @time_temp datetime
set @time_temp = '2014-01-01'
while @time_temp < '2014-01-07'
begin
insert into #tempDate (stat_day) values (CONVERT(varchar(10),@time_temp,121))
set @time_temp= dateadd(d,1,@time_temp)
endSelect
Count(#test.[date]),
#tempDate.stat_day
From
#tempDate
Left Outer Join #test On #tempDate.stat_day = #test.[date]
Group By
#tempDate.stat_day
Select
Count(#test.[date]),
datename(WEEKDAY, #tempDate.stat_day)
From
#tempDate
Left Outer Join #test On #tempDate.stat_day = #test.[date]
Group By
#tempDate.stat_dayIF object_id('tempdb..#test') is not null
BEGIN
drop table #test
ENDIF object_id('tempdb..#tempDate') is not null
BEGIN
drop table #tempDate
END