表结构如下 id,num,date 1 12 2009-6-28
2 12 2009-6-27
3 12 2009-7-1
4 14 2009-7-2
5 14 2009-7-2
6 14 2009-7-2
7 14 2009-7-2
8 14 2009-7-2
9 10 2009-7-4
10 10 2009-7-4
11 15 2009-7-4
12 10 2009-7-4
13 10 2009-7-6
14 10 2009-7-7 ...统计6月27号到7月7号,每天有多少个记录希望统计出以下结果
expr1,expr2
27 1
28 1
29 0
30 0
1 1
2 5
3 0
4 4
5 0
6 1
7 1
2 12 2009-6-27
3 12 2009-7-1
4 14 2009-7-2
5 14 2009-7-2
6 14 2009-7-2
7 14 2009-7-2
8 14 2009-7-2
9 10 2009-7-4
10 10 2009-7-4
11 15 2009-7-4
12 10 2009-7-4
13 10 2009-7-6
14 10 2009-7-7 ...统计6月27号到7月7号,每天有多少个记录希望统计出以下结果
expr1,expr2
27 1
28 1
29 0
30 0
1 1
2 5
3 0
4 4
5 0
6 1
7 1
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-09 07:13:00
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[num] int,[date] Datetime)
Insert tb
Select 1,12,'2009-6-28' union all
Select 2,12,'2009-6-27' union all
Select 3,12,'2009-7-1' union all
Select 4,14,'2009-7-2' union all
Select 5,14,'2009-7-2' union all
Select 6,14,'2009-7-2' union all
Select 7,14,'2009-7-2' union all
Select 8,14,'2009-7-2' union all
Select 9,10,'2009-7-4' union all
Select 10,10,'2009-7-4' union all
Select 11,15,'2009-7-4' union all
Select 12,10,'2009-7-4' union all
Select 13,10,'2009-7-6' union all
Select 14,10,'2009-7-7'
Go
--Select * from tb-->SQL查询如下:
declare @bt datetime,@et datetime
select @bt='2009-06-27',@et='2009-07-07'
select dateadd(dd,a.number,@bt) date,day(dateadd(dd,a.number,@bt)) expr1,count(b.id) expr2
from (
select number
from master..spt_values
where type='p'
and number<datediff(dd,@bt,@et)
) as a
left join tb b
on dateadd(dd,a.number,@bt)=b.date
group by dateadd(dd,a.number,@bt)
order by 1
/*
date expr1 expr2
----------------------- ----------- -----------
2009-06-27 00:00:00.000 27 1
2009-06-28 00:00:00.000 28 1
2009-06-29 00:00:00.000 29 0
2009-06-30 00:00:00.000 30 0
2009-07-01 00:00:00.000 1 1
2009-07-02 00:00:00.000 2 5
2009-07-03 00:00:00.000 3 0
2009-07-04 00:00:00.000 4 4
2009-07-05 00:00:00.000 5 0
2009-07-06 00:00:00.000 6 1
(10 行受影响)
*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-09 07:13:00
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[num] int,[date] Datetime)
Insert tb
Select 1,12,'2009-6-28' union all
Select 2,12,'2009-6-27' union all
Select 3,12,'2009-7-1' union all
Select 4,14,'2009-7-2' union all
Select 5,14,'2009-7-2' union all
Select 6,14,'2009-7-2' union all
Select 7,14,'2009-7-2' union all
Select 8,14,'2009-7-2' union all
Select 9,10,'2009-7-4' union all
Select 10,10,'2009-7-4' union all
Select 11,15,'2009-7-4' union all
Select 12,10,'2009-7-4' union all
Select 13,10,'2009-7-6' union all
Select 14,10,'2009-7-7'
Go
--Select * from tb-->SQL查询如下:
declare @bt datetime,@et datetime
select @bt='2009-06-27',@et='2009-07-07'
select dateadd(dd,a.number,@bt) date,day(dateadd(dd,a.number,@bt)) expr1,count(b.id) expr2
from (
select number
from master..spt_values
where type='p'
and number<=datediff(dd,@bt,@et)
) as a
left join tb b
on dateadd(dd,a.number,@bt)=b.date
group by dateadd(dd,a.number,@bt)
order by 1
/*
date expr1 expr2
----------------------- ----------- -----------
2009-06-27 00:00:00.000 27 1
2009-06-28 00:00:00.000 28 1
2009-06-29 00:00:00.000 29 0
2009-06-30 00:00:00.000 30 0
2009-07-01 00:00:00.000 1 1
2009-07-02 00:00:00.000 2 5
2009-07-03 00:00:00.000 3 0
2009-07-04 00:00:00.000 4 4
2009-07-05 00:00:00.000 5 0
2009-07-06 00:00:00.000 6 1
2009-07-07 00:00:00.000 7 1
(11 行受影响)
*/MODIFY
列 'a.number' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
列 'a.number' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
报了这个错呀
(所影响的行数为 14 行)服务器: 消息 8120,级别 16,状态 1,行 6
列 'a.number' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
declare @bt datetime,@et datetime
select @bt='2009-06-27',@et='2009-07-07'
select dateadd(dd,a.number,@bt) date,day(dateadd(dd,a.number,@bt)) expr1,count(b.id) expr2
from (
select number
from master..spt_values
where type='p'
and number<=datediff(dd,@bt,@et)
) as a
left join tb b
on dateadd(dd,a.number,@bt)=b.date
group by dateadd(dd,a.number,@bt),day(dateadd(dd,a.number,@bt)) ------此處加上day(dateadd(dd,a.number,@bt))
order by 1
(
col datetime
)
go
declare @table table (id int,num int,date datetime)
insert into @table
select 1,12,'2009-6-28' union all
select 2,12,'2009-6-27' union all
select 3,12,'2009-7-1' union all
select 4,14,'2009-7-2' union all
select 5,14,'2009-7-2' union all
select 6,14,'2009-7-2' union all
select 7,14,'2009-7-2' union all
select 8,14,'2009-7-2' union all
select 9,10,'2009-7-4' union all
select 10,10,'2009-7-4' union all
select 11,15,'2009-7-4' union all
select 12,10,'2009-7-4' union all
select 13,10,'2009-7-6' union all
select 14,10,'2009-7-7'declare @begintime datetime
declare @endtime datetime
declare @day int
set @begintime=cast('2009-06-27' as datetime)
set @endtime=cast('2009-07-07' as datetime)
select @day=DATEDIFF(day,@begintime,@endtime)
while @day> =0
begin
insert into # select dateadd(day,@day,@begintime)
set @day=@day-1
end
select cast(substring(convert(varchar(10),col,120),len(convert(varchar(10),col,120))-1,len(convert(varchar(10),col,120))) as int) as expr1,isnull(bb.expr2,0) as expr2 from # left join (select date,count(1) as expr2 from @table group by date) bb
on #.col=bb.date order by #.col drop table #/*
expr1 expr2
----------- -----------
27 1
28 1
29 0
30 0
1 1
2 5
3 0
4 4
5 0
6 1
7 1
*/