想从表UTb_duty_kind1002和表zt_rsdazl 查询出的记录和表UTb_duty_perman1002比较
查询两者不相同的记录,
sql如下,但是提示"关键字 'as' 附近有语法错误。"还有什么高效的写法?select XX.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as XX
where not exists(
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as X
inner join
UTb_duty_perman1002 Y on X.c89=Y.a and X.duty_date=Y.duty_man_date and
X.duty_kind=Y.duty_man_kind and X.flag=Y.flag
查询两者不相同的记录,
sql如下,但是提示"关键字 'as' 附近有语法错误。"还有什么高效的写法?select XX.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as XX
where not exists(
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as X
inner join
UTb_duty_perman1002 Y on X.c89=Y.a and X.duty_date=Y.duty_man_date and
X.duty_kind=Y.duty_man_kind and X.flag=Y.flag
下面修改后没有提示错误但是没有查询出结果,实际上有结果的select XX.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as XX
where not exists(
Select X.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as X
inner join
UTb_duty_perman1002 Y on Y.a=X.c89 and Y.duty_man_date=X.duty_date and
Y.duty_man_kind=X.duty_kind and Y.flag=X.flag)
select XX.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as XX
where not exists
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bminner join
UTb_duty_perman1002 Y on X.c89=Y.a and X.duty_date=Y.duty_man_date and
X.duty_kind=Y.duty_man_kind and X.flag=Y.flag
where T.duty_date between '2011-1-1' and '2011-12-31')
Select X.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as X
inner join
UTb_duty_perman1002 Y on Y.a=X.c89 and Y.duty_man_date=X.duty_date and
Y.duty_man_kind=X.duty_kind and Y.flag=X.flag)看括号里的,里面和外表的表XX没有关联,如果子查询里有记录,那么肯定外边的查询出不来结果,即使有。where not exists(
Select X.* from
(select Z.c89,T.duty_date,T.duty_kind,T.flag
from UTb_duty_kind1002 T left join zt_rsdazl Z on T.teams=Z.bm
where T.duty_date between '2011-1-1' and '2011-12-31') as X
inner join
UTb_duty_perman1002 Y on Y.a=X.c89 and Y.duty_man_date=X.duty_date and
Y.duty_man_kind=X.duty_kind and Y.flag=X.flag
where X.[] = XX.[])试试!
我加了这句:
where XX.c89=x.c89 and XX.duty_date=X.duty_date and XX.duty_kind=X.duty_kind and XX.flag=X.flag
查询出来了