求高手 一条 sql语句
我 有 三 个表 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
*/
;with ShenHeHZ_Flag
as
(
select
BusinessNum,Flag_ManagerCheckCost,count(1) IsExists
from #cost_FeeInfo
group by BusinessNum,Flag_ManagerCheckCost
)
,ShenHeHZ_NoFlag
as
(
select
BusinessNum,count(1) IsExists
from #cost_FeeInfo
group by BusinessNum
)
,ShenHeHZ_F
as
(
select
a.BusinessNum,a.Flag_ManagerCheckCost,a.IsExists IsExists_Flag,b.IsExists IsExists_NoFlag
from ShenHeHZ_Flag a
join ShenHeHZ_NoFlag b on a.BusinessNum=b.BusinessNum
)
,Result
as
(
select
a.[BusinessNum],a.[AdvanceNum],a.[DeliveryListNum],
a.[DeclareDate],a.[Declare_Name],ISNULL(b.IsExists_NoFlag,0) IsExists,'未审核' DataType
from #customs_CustomsDeclaration a
left join ShenHeHZ_F b on a.BusinessNum=b.BusinessNum
where b.Flag_ManagerCheckCost=0
union all
--在这里加了一句,把没有在财务中出现过的,都计算为未审核
select
a.[BusinessNum],a.[AdvanceNum],a.[DeliveryListNum],
a.[DeclareDate],a.[Declare_Name],ISNULL(b.IsExists,0) IsExists,'未审核' DataType
from #customs_CustomsDeclaration a
left join ShenHeHZ_NoFlag b
on a.BusinessNum=b.BusinessNum
where b.[BusinessNum] is null
union all
select
a.[BusinessNum],a.[AdvanceNum],a.[DeliveryListNum],
a.[DeclareDate],a.[Declare_Name],ISNULL(b.IsExists_Flag,0) IsExists,'已审核' DataType
from #customs_CustomsDeclaration a
left join ShenHeHZ_F b on a.BusinessNum=b.BusinessNum
where b.Flag_ManagerCheckCost=1 and b.IsExists_Flag=b.IsExists_NoFlag
union all
select
a.[BusinessNum],a.[AdvanceNum],a.[DeliveryListNum],
a.[DeclareDate],a.[Declare_Name],ISNULL(b.IsExists,0) IsExists,'全部数据' DataType
from #customs_CustomsDeclaration a
left join ShenHeHZ_NoFlag b on a.BusinessNum=b.BusinessNum
)
select
DataType,[BusinessNum],[AdvanceNum],[DeliveryListNum],
[DeclareDate],[Declare_Name],IsExists
from Result order by DataType,BusinessNum
/*
DataTypeBusinessNumAdvanceNumDeliveryListNumDeclareDateDeclare_NameIsExists
全部数据BPO1039424345234322013-01-01 00:00:00.00034320
全部数据RPI20131000001543545345652013-01-05 00:00:00.000reter2
全部数据RPI2013100000161231233243243242013-02-01 00:00:00.000ewrewr2
全部数据RPO201310000001NULLNULL2013-01-02 00:00:00.000NULL2
全部数据YLO2013100015394345234322013-01-01 00:00:00.00034320
未审核BPO1039424345234322013-01-01 00:00:00.00034320
未审核RPI20131000001543545345652013-01-05 00:00:00.000reter2
未审核RPO201310000001NULLNULL2013-01-02 00:00:00.000NULL2
未审核YLO2013100015394345234322013-01-01 00:00:00.00034320
已审核RPI2013100000161231233243243242013-02-01 00:00:00.000ewrewr2
*/
