--例如查今天没有做transaction的账号!
declare @date datetime
select @date=convert(varchar(10),getdate(),120)
select distinct a.* from A left join (select * from B where [date] between @date and @date+1) b on a.A1=b.A1 and a.A2=b.A2 and a.A3=b.A3 and a.A4=b.A4 and a.A5=b.A5
declare @date datetime
select @date=convert(varchar(10),getdate(),120)
select distinct a.* from A left join (select * from B where [date] between @date and @date+1) b on a.A1=b.A1 and a.A2=b.A2 and a.A3=b.A3 and a.A4=b.A4 and a.A5=b.A5
from a
where not exists (select 1
from b
where A1 = a.A1
and A2 = a.A2
and A3 = a.A3
and A4 = a.A4
and A5 = a.A5
and date = '2004-1-1'
)
from a
left join b
on a.A1=b.A1 and a.A2=b.A2 and a.A3=b.A3 and a.A4=b.A4 and a.A5=b.A5
where date is null or amount is null
1.不懂,select 1 from b where A1 = a.A1
and A2 = a.A2
and A3 = a.A3
and A4 = a.A4
and A5 = a.A5
and date = '2004-1-1',这句话到底选的什么东西,1在where中用过
但在字段中没有用过,难道是选取很多1出来?2.data和amount不可能是null的阿,我知道老大的意思,是不是
select distinct a.*
from a
left join b
on a.A1=b.A1 and a.A2=b.A2 and a.A3=b.A3 and a.A4=b.A4 and a.A5=b.A5
where b.date is null or b.amount is null
from
(
select [date]
from b
group by [date]
) T
left join a
on 1 = 1
left join b
on a.A1=b.A1 and a.A2=b.A2 and a.A3=b.A3 and a.A4=b.A4 and a.A5=b.A5 and t.date = b.date
where b.a1 is null --and t.date = '2004-1-1'
order by t.date
from
(
select [date]
from b
group by [date]
) T
left join a
on 1 = 1
left join b
on a.A1=b.A1 and a.A2=b.A2 and a.A3=b.A3 and a.A4=b.A4 and a.A5=b.A5 and t.date = b.date
where b.a1 is null --and t.date = '2004-1-4'
order by t.dateselect *
from
(
select [date]
from b
group by [date]
) T
left join a
on 1 = 1
where not exists (select 1
from b
where A1 = a.A1
and A2 = a.A2
and A3 = a.A3
and A4 = a.A4
and A5 = a.A5
and date = t.date
) --and t.date = '2004-1-4'
我觉得tuwicn所得有点道理,playyuer的可以用转换成char后,然后用-连接后判断,好象应该可以
明天测试
playyuer老大第一个解法的很巧妙,但有一点疑问,当碰到a-b一对多的情况,会不会连出多条记录呢?
下面一个不懂,请老大解释一下
通常会被用来比较执行效率!
tuwicn 的方法考虑不全面!如果 a1+a2+a3+a4+a5 = b1+b2+b3+b4+b5
不能确保:
a1 = b1
a2 = b2
a3 = b3
a4 = b4
a5 = b5
left join一定要考虑效率