--B表是多余的吗?
create table A(ID varchar(10),Country varchar(10),StartDate datetime,EndDate datetime)
insert into A values('Jerry','U.S.A' ,'2007-12-04','2007-12-11')
insert into A values('Tom' ,'England','2007-12-04','2007-12-11')
insert into A values('Jack' ,'France' ,'2007-12-04','2007-12-11')
create table C(Country varchar(10),Date datetime)
insert into C values('U.S.A' ,'2007-12-08')
insert into C values('U.S.A' ,'2007-12-09')
insert into C values('England','2007-12-07')
insert into C values('England','2007-12-08')
insert into C values('England','2007-12-08')
insert into C values('France' ,'2007-12-08')
goselect a.* , 工作天数 = datediff(day , StartDate , EndDate) - t.cnt from a,
(select a.country , cnt = sum(case when c.Date >= a.StartDate and c.date <= a.EndDate then 1 else 0 end) from a,c where a.country = c.country group by a.country) t
where a.country = t.countrydrop table A,C/*
ID Country StartDate EndDate 工作天数
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ -----------
Jerry U.S.A 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 5
Tom England 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 4
Jack France 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 6(所影响的行数为 3 行)
*/
create table A(ID varchar(10),Country varchar(10),StartDate datetime,EndDate datetime)
insert into A values('Jerry','U.S.A' ,'2007-12-04','2007-12-11')
insert into A values('Tom' ,'England','2007-12-04','2007-12-11')
insert into A values('Jack' ,'France' ,'2007-12-04','2007-12-11')
create table C(Country varchar(10),Date datetime)
insert into C values('U.S.A' ,'2007-12-08')
insert into C values('U.S.A' ,'2007-12-09')
insert into C values('England','2007-12-07')
insert into C values('England','2007-12-08')
insert into C values('England','2007-12-08')
insert into C values('France' ,'2007-12-08')
goselect a.* , 工作天数 = datediff(day , StartDate , EndDate) - t.cnt from a,
(select a.country , cnt = sum(case when c.Date >= a.StartDate and c.date <= a.EndDate then 1 else 0 end) from a,c where a.country = c.country group by a.country) t
where a.country = t.countrydrop table A,C/*
ID Country StartDate EndDate 工作天数
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ -----------
Jerry U.S.A 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 5
Tom England 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 4
Jack France 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 6(所影响的行数为 3 行)
*/
ID,StartDate,EndDate
Jerry,2007-12-04,2007-12-11
Tom,2007-12-04,2007-12-11
Jack,2007-12-04,2007-12-11 sorry,A表的记录应该是没有国籍的
create table A(ID varchar(10),StartDate datetime,EndDate datetime)
insert into A values('Jerry','2007-12-04','2007-12-11')
insert into A values('Tom' ,'2007-12-04','2007-12-11')
insert into A values('Jack' ,'2007-12-04','2007-12-11')
create table B(ID varchar(10),Country varchar(10),Phone varchar(10))
insert into B values('Jerry','U.S.A' ,'21-023-098')
insert into B values('Tom' ,'England','30-043-088')
insert into B values('Jack' ,'France' ,'40-023-098')
create table C(Country varchar(10),Date datetime)
insert into C values('U.S.A' ,'2007-12-08')
insert into C values('U.S.A' ,'2007-12-09')
insert into C values('England','2007-12-07')
insert into C values('England','2007-12-08')
insert into C values('England','2007-12-08')
insert into C values('France' ,'2007-12-08')
goselect a.* , b.country , b.phone , 工作天数 = datediff(day , a.StartDate , a.EndDate) - t.cnt from a,b,
(select c.country , cnt = sum(case when c.Date >= a.StartDate and c.date <= a.EndDate then 1 else 0 end) from a,b,c where a.id = b.id and c.country = b.country group by c.country) t
where a.id = b.id and b.country = t.countrydrop table A,B,C/*
ID StartDate EndDate country phone 工作天数
---------- ------------------------------------------------------ ------------------------------------------------------ ---------- ---------- -----------
Jerry 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 U.S.A 21-023-098 5
Tom 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 England 30-043-088 4
Jack 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 France 40-023-098 6(所影响的行数为 3 行)
*/
create table A(ID varchar(10),StartDate datetime,EndDate datetime)
insert into A values('Jerry','2007-12-04','2007-12-11')
insert into A values('Tom' ,'2007-12-04','2007-12-11')
insert into A values('Jack' ,'2007-12-04','2007-12-11')
create table B(ID varchar(10),Country varchar(10),Phone varchar(10))
insert into B values('Jerry','U.S.A' ,'21-023-098')
insert into B values('Tom' ,'England','30-043-088')
insert into B values('Jack' ,'France' ,'40-023-098')
create table C(Country varchar(10),Date datetime)
insert into C values('U.S.A' ,'2007-12-08')
insert into C values('U.S.A' ,'2007-12-09')
insert into C values('England','2007-12-07')
insert into C values('England','2007-12-08')
insert into C values('England','2007-12-08')
insert into C values('France' ,'2007-12-08')
goselect a.* , b.country , b.phone , 工作天数 = datediff(day , a.StartDate , a.EndDate) + 1 - t.cnt from a,b,
(select c.country , cnt = sum(case when c.Date >= a.StartDate and c.date <= a.EndDate then 1 else 0 end) from a,b,c where a.id = b.id and c.country = b.country group by c.country) t
where a.id = b.id and b.country = t.countrydrop table A,B,C/*
ID StartDate EndDate country phone 工作天数
---------- ------------------------------------------------------ ------------------------------------------------------ ---------- ---------- -----------
Jerry 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 U.S.A 21-023-098 6
Tom 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 England 30-043-088 5
Jack 2007-12-04 00:00:00.000 2007-12-11 00:00:00.000 France 40-023-098 7(所影响的行数为 3 行)
*/
declare @A table (id varchar(10),startdate datetime,enddate datetime)
insert into @A
select 'Jerry','2007-12-04','2007-12-11'
union
select 'Tom','2007-12-04','2007-12-11'
union
select 'Jack','2007-12-04','2007-12-11'declare @B table (id varchar(10),country varchar(10),phone varchar(20))
insert into @B
select 'Jerry','U.S.A','21-023-098'
union
select 'Tom','England','30-043-088'
union
select 'Jack','France','40-023-098'declare @C table (country varchar(10),leavedate datetime)
insert into @C
select 'U.S.A','2007-12-08'
union
select 'U.S.A','2007-12-09'
union
select 'England','2007-12-07'
union
select 'England','2007-12-08'
union
select 'England','2007-12-09'
union
select 'France','2007-12-08'
select a.ID,a.startdate,a.enddate,d.country,datediff(day,a.startdate,a.enddate)+1-num as 工作天数
from @A a,
(select a.id,b.country,count(a.id) num
from @A a,@B b,@C c
where a.ID=b.ID
and b.country=c.country
and c.leavedate>=a.startdate
and c.leavedate<=a.enddate
group by a.id,b.country) d
where a.ID=d.ID
drop table hexiangrong_yssAcreate table hexiangrong_yssA(ID varchar(10) ,StartDate datetime,EndDate datetime)
insert into hexiangrong_yssA
select 'Jerry','2007-12-04','2007-12-11' union all
select 'Tom','2007-12-04','2007-12-11' union all
select 'Jack','2007-12-04','2007-12-11' drop table hexiangrong_yssB
create table hexiangrong_yssB(ID varchar(10),Country varchar(10),Phone varchar(20))
insert into hexiangrong_yssB
select 'Jerry','U.S.A','21-023-098' union all
select 'Tom','England','30-043-088' union all
select 'Jack','France','40-023-098' drop table hexiangrong_yssC
create table hexiangrong_yssC(Country varchar(10),Date varchar(10))insert into hexiangrong_yssC
select 'U.S.A','2007-12-08' union all
select 'U.S.A','2007-12-09' union all
select 'England','2007-12-07' union all
select 'England','2007-12-08' union all
select 'England','2007-12-09' union all
select 'France','2007-12-08'select A.id, (datediff(day,A.StartDate, A.EndDate)+1-B.cnt) as tian from hexiangrong_yssA A join (select
A.id,
Count(1) as cnt
from hexiangrong_yssA A inner join hexiangrong_yssB B
on A.id = B.id
right join hexiangrong_yssC C
on C.Country = B.Country
where
A.StartDate <=C.Date
and C.Date <=A.EndDate
group by A.id) Bon A.id = B.id