关于联合查询的问题

jycnet 2007-12-11 10:59:39

请教各位：

我有表A和表B
name,sdate
aaa,2007-10-11
aaa,2007-10-12
aaa,2007-10-13
aaa,2007-10-14
aaa,2007-10-15
aaa,2007-10-16
aaa,2007-10-17
aaa,2007-10-18
aaa,2007-10-19
aaa,2007-10-20
aaa,2007-10-21
bbb,2007-10-11
bbb,2007-10-12
bbb,2007-10-13
bbb,2007-10-14
bbb,2007-10-15
bbb,2007-10-16
bbb,2007-10-17
bbb,2007-10-18
bbb,2007-10-19
bbb,2007-10-20
bbb,2007-10-21

表B
name,start_date,end_date
aaa, 2007-10-12,2007-10-15
aaa,2007-10-18,2007-10-20
bbb,2007-10-14,2007-10-17

我想通过查询得到这样一个表

表C
name,sdate,fix
aaa,2007-10-11,0
aaa,2007-10-12,1
aaa,2007-10-13,1
aaa,2007-10-14,1
aaa,2007-10-15,1
aaa,2007-10-16,0
aaa,2007-10-17,0
aaa,2007-10-18,1
aaa,2007-10-19,1
aaa,2007-10-20,1
aaa,2007-10-21,0
bbb,2007-10-11,0
bbb,2007-10-12,0
bbb,2007-10-13,0
bbb,2007-10-14,1
bbb,2007-10-15,1
bbb,2007-10-16,1
bbb,2007-10-17,1
bbb,2007-10-18,0
bbb,2007-10-19,0
bbb,2007-10-20,0
bbb,2007-10-21,0

该如何写查询？

...全文

96 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

jycnet 2007-12-11

打赏
举报

非常感谢chuifengde，和dawugui ，又学到东西了，结贴给分

chuifengde 2007-12-11

打赏
举报

declare @a table(name varchar(20),sdate smalldatetime)

insert @a select 'aaa','2007-10-11' 

union all select 'aaa','2007-10-12' 

union all select 'aaa','2007-10-13' 

union all select 'aaa','2007-10-14' 

union all select 'aaa','2007-10-15' 

union all select 'aaa','2007-10-16' 

union all select 'aaa','2007-10-17' 

union all select 'aaa','2007-10-18' 

union all select 'aaa','2007-10-19' 

union all select 'aaa','2007-10-20' 

union all select 'aaa','2007-10-21' 

union all select 'bbb','2007-10-11' 

union all select 'bbb','2007-10-12' 

union all select 'bbb','2007-10-13' 

union all select 'bbb','2007-10-14' 

union all select 'bbb','2007-10-15' 

union all select 'bbb','2007-10-16' 

union all select 'bbb','2007-10-17' 

union all select 'bbb','2007-10-18' 

union all select 'bbb','2007-10-19' 

union all select 'bbb','2007-10-20' 

union all select 'bbb','2007-10-21' 



declare @b table(name varchar(20),start_date smalldatetime,end_date smalldatetime)

insert @b select 'aaa','2007-10-12','2007-10-15' 

union all select 'aaa','2007-10-18','2007-10-20' 

union all select 'bbb','2007-10-14','2007-10-17' 



select *,case when exists(select 1 from @b where name=a.name and sdate between start_date and end_date) then 1 else 0 end

from @a a 

--result

/*name                 sdate                                                              

-------------------- ------------------------------------------------------ ----------- 

aaa                  2007-10-11 00:00:00                                    0

aaa                  2007-10-12 00:00:00                                    1

aaa                  2007-10-13 00:00:00                                    1

aaa                  2007-10-14 00:00:00                                    1

aaa                  2007-10-15 00:00:00                                    1

aaa                  2007-10-16 00:00:00                                    0

aaa                  2007-10-17 00:00:00                                    0

aaa                  2007-10-18 00:00:00                                    1

aaa                  2007-10-19 00:00:00                                    1

aaa                  2007-10-20 00:00:00                                    1

aaa                  2007-10-21 00:00:00                                    0

bbb                  2007-10-11 00:00:00                                    0

bbb                  2007-10-12 00:00:00                                    0

bbb                  2007-10-13 00:00:00                                    0

bbb                  2007-10-14 00:00:00                                    1

bbb                  2007-10-15 00:00:00                                    1

bbb                  2007-10-16 00:00:00                                    1

bbb                  2007-10-17 00:00:00                                    1

bbb                  2007-10-18 00:00:00                                    0

bbb                  2007-10-19 00:00:00                                    0

bbb                  2007-10-20 00:00:00                                    0

bbb                  2007-10-21 00:00:00                                    0

*/

dawugui 2007-12-11

打赏
举报

create table A (name varchar(10),sdate datetime)

insert into A values('aaa','2007-10-11') 

insert into A values('aaa','2007-10-12') 

insert into A values('aaa','2007-10-13') 

insert into A values('aaa','2007-10-14') 

insert into A values('aaa','2007-10-15') 

insert into A values('aaa','2007-10-16') 

insert into A values('aaa','2007-10-17') 

insert into A values('aaa','2007-10-18') 

insert into A values('aaa','2007-10-19') 

insert into A values('aaa','2007-10-20') 

insert into A values('aaa','2007-10-21') 

insert into A values('bbb','2007-10-11') 

insert into A values('bbb','2007-10-12') 

insert into A values('bbb','2007-10-13') 

insert into A values('bbb','2007-10-14') 

insert into A values('bbb','2007-10-15') 

insert into A values('bbb','2007-10-16') 

insert into A values('bbb','2007-10-17') 

insert into A values('bbb','2007-10-18') 

insert into A values('bbb','2007-10-19') 

insert into A values('bbb','2007-10-20') 

insert into A values('bbb','2007-10-21') 

create table B(name varchar(10),start_date datetime,end_date datetime)

insert into B values('aaa','2007-10-12','2007-10-15') 

insert into B values('aaa','2007-10-18','2007-10-20') 

insert into B values('bbb','2007-10-14','2007-10-17') 

go



select top 8000 identity(int,0,1) as id into tmp from syscolumns a,syscolumns b



select a.* , isnull(t.cnt,0) fix from A left join

(select b.name , dateadd(day , c.id , b.start_date) start_date , count(*) cnt from B ,tmp C where dateadd(day , C.id , b.start_date) <= b.end_date group by b.name , dateadd(day , c.id , b.start_date)) t

on a.name = t.name and a.sdate = t.start_date

order by a.name , a.sdate



drop table a,b,tmp



/*

name       sdate                                                  fix         

---------- ------------------------------------------------------ ----------- 

aaa        2007-10-11 00:00:00.000                                0

aaa        2007-10-12 00:00:00.000                                1

aaa        2007-10-13 00:00:00.000                                1

aaa        2007-10-14 00:00:00.000                                1

aaa        2007-10-15 00:00:00.000                                1

aaa        2007-10-16 00:00:00.000                                0

aaa        2007-10-17 00:00:00.000                                0

aaa        2007-10-18 00:00:00.000                                1

aaa        2007-10-19 00:00:00.000                                1

aaa        2007-10-20 00:00:00.000                                1

aaa        2007-10-21 00:00:00.000                                0

bbb        2007-10-11 00:00:00.000                                0

bbb        2007-10-12 00:00:00.000                                0

bbb        2007-10-13 00:00:00.000                                0

bbb        2007-10-14 00:00:00.000                                1

bbb        2007-10-15 00:00:00.000                                1

bbb        2007-10-16 00:00:00.000                                1

bbb        2007-10-17 00:00:00.000                                1

bbb        2007-10-18 00:00:00.000                                0

bbb        2007-10-19 00:00:00.000                                0

bbb        2007-10-20 00:00:00.000                                0

bbb        2007-10-21 00:00:00.000                                0



（所影响的行数为 22 行）

*/