假如有兩表a, b
a的結構
key id member dateF dateT
1 1 1 2006-11-12 2006-11-15
2 1 2 2006-11-12 2006-11-15
3 2 3 2006-11-15 2006-11-16b表的結構
ddateF dmember ddisc
2006-11-12 1 -1
2006-11-12 3 -1現在想用一條SQL語句求出a表中dateF與dateT之間的天數, 如果日期在b表中存在的則天數減去ddisc的數 , 還要根據member與dmember相同的才減去.SQL出的效果應該是 key id member day
1 1 1 2
2 1 2 3
3 2 3 1
謝謝!
a的結構
key id member dateF dateT
1 1 1 2006-11-12 2006-11-15
2 1 2 2006-11-12 2006-11-15
3 2 3 2006-11-15 2006-11-16b表的結構
ddateF dmember ddisc
2006-11-12 1 -1
2006-11-12 3 -1現在想用一條SQL語句求出a表中dateF與dateT之間的天數, 如果日期在b表中存在的則天數減去ddisc的數 , 還要根據member與dmember相同的才減去.SQL出的效果應該是 key id member day
1 1 1 2
2 1 2 3
3 2 3 1
謝謝!
insert into @a select 1,1,1,'2006-11-12','2006-11-15'
insert into @a select 2,1,2,'2006-11-12','2006-11-15'
insert into @a select 3,2,3,'2006-11-15','2006-11-16'declare @b table(ddateF datetime,dmember int,ddisc int)
insert into @b select '2006-11-12',1,-1
insert into @b select '2006-11-12',3,-1select
a.[key],a.id,a.member,datediff(day,a.dateF,a.dateT)+isnull(b.ddisc,0) as [day]
from
@a a
left join
@b b
on
a.member=b.dmember/*
key id member day
----------- ----------- ----------- -----------
1 1 1 2
2 1 2 3
3 2 3 0*/
INSERT INTO @A
SELECT 1, 1, 1, '2006-11-12', '2006-11-15'
UNION ALL SELECT 2, 1, 2, '2006-11-12', '2006-11-15'
UNION ALL SELECT 3, 2, 3, '2006-11-15', '2006-11-16'
DECLARE @B TABLE([ddateF] datetime, [dmember] int, [ddisc] int)
INSERT INTO @B
SELECT '2006-11-12', 1, -1
UNION ALL SELECT '2006-11-12', 3, -1SELECT A.[key], A.[id], A.member, DATEDIFF(DAY, A.dateF, A.dateT) + ISNULL(B.ddisc, 0)
FROM @A A
LEFT JOIN @B B
ON A.member = B.dmember AND A.dateF = B.ddateF
--或者
SELECT A.[key], A.[id], A.member, DATEDIFF(DAY, A.dateF, A.dateT) + ISNULL(B.ddisc, 0)
FROM @A A
LEFT JOIN @B B
ON A.member = B.dmember AND B.ddateF BETWEEN A.dateF AND A.dateT
id,
member,
datediff(day,dateF,dateT)-isnull(ddisc,0) as [day]
from (
select * from a left join b on a.member=b.dmember and b.ddateF between a.dateF and a.dateT
) T
insert into @t select 1,1,1,'2006-11-12','2006-11-15'
insert into @t select 2,1,2,'2006-11-12','2006-11-15'
insert into @t select 3,2,3,'2006-11-15','2006-11-16'declare @tt table(ddateF datetime,dmember int,ddisc int)
insert into @tt select '2006-11-12',1,-1
insert into @tt select '2006-11-12',3,-1select
a.keyy,a.id,a.member,datediff(day,a.dateF,a.dateT)+isnull(b.ddisc,0) as [day]
from
@t a ,@tt b
where a.member=b.dmember
---------
key id member day 1 1 1 2
3 2 3 0
datediff(day,a.dateF,a.dateT) + +isnull(b.ddisc,0) as [day]
from a
left join b
on a.member = b.dmember
and a.dateF = b.ddateF
insert into @t1 select 2, 1, 2, '2006-11-12', '2006-11-15'
insert into @t1 select 3, 2, 3, '2006-11-15', '2006-11-16'declare @t2 table(ddateF datetime , dmember int , ddisc int)insert into @t2 select '2006-11-12', 1, 1
insert into @t2 select '2006-11-12', 3, 1select a.key1 as [key] , a.id , a.member , datediff(day,a.datef,a.datet) - isnull(b.ddisc,0) as day
from @t1 a left join @t2 b on a.member = b.dmember and b.ddatef >= a.datef and b.ddatef <= a.datet
key id member day
----------- ----------- ----------- -----------
1 1 1 2
2 1 2 3
3 2 3 1(所影响的行数为 3 行)
insert into @t1 select 2, 1, 2, '2006-11-12', '2006-11-15'
insert into @t1 select 3, 2, 3, '2006-11-15', '2006-11-16'declare @t2 table(ddateF datetime , dmember int , ddisc int)insert into @t2 select '2006-11-12', 1, 1
insert into @t2 select '2006-11-12', 3, 1select a.[key] , a.id , a.member , datediff(day,a.datef,a.datet) - isnull(b.ddisc,0) as day
from @t1 a left join @t2 b on a.member = b.dmember and b.ddatef = a.datefkey id member day
----------- ----------- ----------- -----------
1 1 1 2
2 1 2 3
3 2 3 1(所影响的行数为 3 行)
insert A select 1, 1, 1, '2006-11-12', '2006-11-15'
union all select 2, 1, 2, '2006-11-12', '2006-11-15'
union all select 3, 2, 3, '2006-11-15', '2006-11-16'
create table B(ddateF datetime, dmember int, ddisc int)
insert B select '2006-11-12', 1, -1
union all select '2006-11-12' , 3, -1select A.*, datediff(d, dateF, dateT)+isnull(tmp.ddisc, 0) days from A
left join(
select ddateF, dmember, sum(ddisc) as ddisc from B group by ddateF, dmember
) tmp on A.dateF=tmp.ddateF and A.member=tmp.dmember
drop table A
drop table B--result
key id member dateF dateT days
----------- ----------- ----------- ------------------------------------------------------ ------------------------------------------------------ -----------
1 1 1 2006-11-12 00:00:00.000 2006-11-15 00:00:00.000 2
2 1 2 2006-11-12 00:00:00.000 2006-11-15 00:00:00.000 3
3 2 3 2006-11-15 00:00:00.000 2006-11-16 00:00:00.000 1(3 row(s) affected)