希望利用表a和表b 求出下面的结果
表a
enter_time sid
09/10/2008 s1
09/10/2008 s10
09/10/2008 s10
09/10/2008 s30
09/10/2008 s40
09/10/2008 s40
09/11/2008 s11
09/11/2008 s20
09/11/2008 s20
09/11/2008 s30
表b
sid name
s1 a
s10 b
s11 c
s20 d
s21 e
s30 f
s40 g结果
enter_time sid name count(*)
09/10/2008 s1 a 1
09/10/2008 s10 b 2
09/10/2008 s11 c 0
09/10/2008 s20 d 0
09/10/2008 s21 e 0
09/10/2008 s30 f 1
09/10/2008 s40 g 2
09/11/2008 s1 a 0
09/11/2008 s10 b 0
09/11/2008 s11 c 1
09/11/2008 s20 d 2
09/11/2008 s21 e 0
09/11/2008 s30 f 1
09/11/2008 s40 g 0
表a
enter_time sid
09/10/2008 s1
09/10/2008 s10
09/10/2008 s10
09/10/2008 s30
09/10/2008 s40
09/10/2008 s40
09/11/2008 s11
09/11/2008 s20
09/11/2008 s20
09/11/2008 s30
表b
sid name
s1 a
s10 b
s11 c
s20 d
s21 e
s30 f
s40 g结果
enter_time sid name count(*)
09/10/2008 s1 a 1
09/10/2008 s10 b 2
09/10/2008 s11 c 0
09/10/2008 s20 d 0
09/10/2008 s21 e 0
09/10/2008 s30 f 1
09/10/2008 s40 g 2
09/11/2008 s1 a 0
09/11/2008 s10 b 0
09/11/2008 s11 c 1
09/11/2008 s20 d 2
09/11/2008 s21 e 0
09/11/2008 s30 f 1
09/11/2008 s40 g 0
表a 日志表 字段: 时间 日志类型
表b 日志类型表 字段 类型id 类型名称统计每天各种类型的日志的数量,但是要求即时当天没有这种类型的日志,同样要显示出来,数量为0
楼上的是对的,但是enter_time和a.sid应该调换下位置,这表示先按enter_time分组,再按a.sid分组
from b cross join (select distinct enter_time from a) c left join a on b.sid=a.sid and c.enter_time=a.enter_time
group by c.enter_time,b.sid,b.name
order by 1,2
Set Nocount On
declare @1 table([enter_time] Datetime,[sid] nvarchar(3))
Insert @1
select '09/10/2008',N's1' union all
select '09/10/2008',N's10' union all
select '09/10/2008',N's10' union all
select '09/10/2008',N's30' union all
select '09/10/2008',N's40' union all
select '09/10/2008',N's40' union all
select '09/11/2008',N's11' union all
select '09/11/2008',N's20' union all
select '09/11/2008',N's20' union all
select '09/11/2008',N's30'
declare @2 table([sid] nvarchar(3),[name] nvarchar(1))
Insert @2
select N's1',N'a' union all
select N's10',N'b' union all
select N's11',N'c' union all
select N's20',N'd' union all
select N's21',N'e' union all
select N's30',N'f' union all
select N's40',N'g'
Declare @BeginDate datetime,@EndDate datetime
Select @BeginDate='20080910',@EndDate='20080911'--1
;With
t0 As(Select id=1 Union All Select id=1),
t1 As(Select id=a.id From t0 a,t0 b),
t2 As(Select id=a.id From t1 a,t1 b),
t3 As(Select id=a.id From t2 a,t2 b),
t4 As(Select id=a.id From t3 a,t3 b),
t5 As(Select id=Row_number() Over(Order By a.id) From t4 a,t4 b),
ta As(Select enter_time=Dateadd(day,id-1,@BeginDate) From t5 Where id<=Datediff(day,@BeginDate,@EndDate)+1)
Select enter_time, sid,name,Total
From ta a Cross Join @2 b
Outer Apply(Select Total=(Select Count(*) From @1 Where sid=b.sid And enter_time=a.enter_time)) p--2
;With t
As
(
Select enter_time=@BeginDate
Union All
Select enter_time=enter_time+1 From t Where enter_time<@EndDate
)
Select enter_time, sid,name,Total
From t a Cross Join @2 b
Outer Apply(Select Total=(Select Count(*) From @1 Where sid=b.sid And enter_time=a.enter_time)) p
Option(Maxrecursion 0)--3在2000就使用临时表都可以实现/*
enter_time sid name Total
----------------------- ---- ---- -----------
2008-09-10 00:00:00.000 s1 a 1
2008-09-10 00:00:00.000 s10 b 2
2008-09-10 00:00:00.000 s11 c 0
2008-09-10 00:00:00.000 s20 d 0
2008-09-10 00:00:00.000 s21 e 0
2008-09-10 00:00:00.000 s30 f 1
2008-09-10 00:00:00.000 s40 g 2
2008-09-11 00:00:00.000 s1 a 0
2008-09-11 00:00:00.000 s10 b 0
2008-09-11 00:00:00.000 s11 c 1
2008-09-11 00:00:00.000 s20 d 2
2008-09-11 00:00:00.000 s21 e 0
2008-09-11 00:00:00.000 s30 f 1
2008-09-11 00:00:00.000 s40 g 0
*/
declare @a table(enter_time datetime,sid nvarchar(20))
insert into @a
select '2008-09-10','s1' union all
select '2008-09-10','s10' union all
select '2008-09-10','s10' union all
select '2008-09-10','s30' union all
select '2008-09-10','s40' union all
select '2008-09-10','s40' union all
select '2008-09-11','s11' union all
select '2008-09-11','s20' union all
select '2008-09-11','s20' union all
select '2008-09-11','s30'
declare @b table(sid nvarchar(20),[name] nvarchar(20))
insert into @b
select 's1','a' union all
select 's10','b' union all
select 's11','c' union all
select 's20','d' union all
select 's21','e' union all
select 's30','f' union all
select 's40','g'
select distinct c.*,isnull(d.[count],0) as [count] from
(select a.enter_time,b.sid,b.[name]
from @a a,@b b) c left join (select e.*,count(1) as [count] from @a e group by enter_time,sid) d
on c.enter_time = d.enter_time and c.sid = d.sid
(
enter_time varchar(30),
sid varchar(10)
)insert ta select '09/10/2008','s1' union all select '09/10/2008','s10'
union all select '09/10/2008','s10'
union all select '09/10/2008','s30'
union all select '09/10/2008','s40'
union all select '09/10/2008','s40'
union all select '09/11/2008','s11'
union all select '09/11/2008','s20'
union all select '09/11/2008','s20'
union all select '09/11/2008','s30'-------------------
create table tb
(
sid varchar(10),
name char(4)
)insert tb select's1','a' union all select 's10','b'
union all select 's11','c'
union all select 's20','d'
union all select 's21','e'
union all select 's30','f'
union all select 's40','g'select b.enter_time,b.sid,b.name,count(a.sid)con from ta as a right join
(select a.enter_time,sid,name from (select enter_time from ta group by enter_time) as a
cross join tb as b
)as b
on a.enter_time = b.enter_time and a.sid=b.sid
group by b.enter_time,b.sid,b.name