导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

关于联合查询的问题

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

该如何写查询?
...全文
67 点赞 收藏 4
写回复
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 行)
*/
回复
swsky 2007-12-11
不知道你那个fix字段是怎么来的?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告