我 有 三 个表 customs_CustomsDeclaration（报关），bus_BusinessDetail(业务)，cost_FeeInfo(财务)
我想查询出 那票 报关单 没有 录入 费用
如
SELECT AdvanceNum,DeliveryListNum,Consignor,DeclareDate,Declare_Name,bbd.BusinessNum
,Flag_Customs ，IsExists--表示 是否 存在费用
(SELECT AdvanceNum,DeliveryListNum,Consignor,DeclareDate,Declare_Name,bbd.BusinessNum
,Flag_Customs
from customs_CustomsDeclaration as ccd ,bus_BusinessDetail as bbd
where bbd.BusinessNum = ccd.BusinessNum and bbd.BusinessNum in
( select BusinessNum from (select b.BusinessNum,count(*) as totalNum,
SUM(case when f.Flag_ManagerCheckCost= '0' then 1 else 0 end)
as checkNum
from bus_BusinessDetail b inner join cost_FeeInfo f on b.BusinessNum=f.BusinessNum
group by b.BusinessNum ) t
where totalNum>0 and checkNum > 0
UNION
SELECT BusinessNum FROM customs_CustomsDeclaration
WHERE BusinessNum NOT IN ( SELECT BusinessNum FROM cost_FeeInfo )
and InputDate BETWEEN '2013-10-23 00:00:00' AND '2013-10-30 00:00:00' group by BusinessNum )
AND ccd.InputDate BETWEEN '2013-10-23 00:00:00' AND '2013-10-30 00:00:00' AND ccd.IsAvailable = '1'
group by AdvanceNum,DeliveryListNum,Consignor,DeclareDate,Declare_Name,bbd.BusinessNum,Flag_Customs)
求高手 解答 ，我 写了 好久都没 写出来
------解决方案--------------------
;WITH?Checked?AS?(
SELECT?*,'已审核'?AS?[状态],(SELECT?COUNT(?businessnum?)?FROM?#cost_FeeInfo?WHERE?#cost_FeeInfo.businessnum=a.businessnum)IsExists
FROM?#customs_CustomsDeclaration?a?
WHERE?EXISTS?(SELECT?1?FROM?#cost_FeeInfo?b?WHERE?a.BusinessNum=b.BusinessNum?AND?b.Flag_ManagerCheckCost=1)
AND?NOT?EXISTS?(SELECT?1?FROM?#cost_FeeInfo?b?WHERE?a.BusinessNum=b.BusinessNum?AND?b.Flag_ManagerCheckCost=0))
SELECT?*,'未审核'?AS?[状态],(SELECT?COUNT(?businessnum?)?FROM?#cost_FeeInfo?WHERE?#cost_FeeInfo.businessnum=#customs_CustomsDeclaration.businessnum)?IsExists
FROM?#customs_CustomsDeclaration?WHERE?businessnum?NOT?IN?(SELECT?businessnum?FROM?Checked)
UNION?ALL?
SELECT?*?FROM?Checked
/*
Id??????????BusinessNum??????????AdvanceNum???????????DeclareDate?????????????DeliveryListNum????????????????????????????????????Declare_Name?状态?????IsExists
-----------?--------------------?--------------------?-----------------------?--------------------------------------------------?------------?------?-----------
1???????????RPO201310000001??????NULL?????????????????2013-01-02?00:00:00.000?NULL???????????????????????????????????????????????NULL?????????未审核????2
2???????????RPI201310000015??????43545????????????????2013-01-05?00:00:00.000?34565??????????????????????????????????????????????reter????????未审核????2
4???????????BPO103942????????????4345?????????????????2013-01-01?00:00:00.000?23432??????????????????????????????????????????????3432?????????未审核????0
5???????????YLO201310001539??????4345?????????????????2013-01-01?00:00:00.000?23432??????????????????????????????????????????????3432?????????未审核????0
3???????????RPI201310000016??????123123???????????????2013-02-01?00:00:00.000?324324324??????????????????????????????????????????ewrewr???????已审核????2