求好手 帮助

求高手 帮助

if OBJECT_ID('tempdb.dbo.#bus_BusinessDetail') is not null drop table #bus_BusinessDetail
if OBJECT_ID('tempdb.dbo.#cost_FeeInfo') is not null drop table #cost_FeeInfo
if OBJECT_ID('tempdb.dbo.#customs_CustomsDeclaration') is not null drop table #customs_CustomsDeclaration
CREATE TABLE [#bus_BusinessDetail](     
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[BusinessNum] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,     
[Consignor] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,  
CONSTRAINT [PK_BUS_BUSINESSDETAIL] PRIMARY KEY NONCLUSTERED  
(     [Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]   


CREATE TABLE [#cost_FeeInfo](     
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[BusinessNum] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,     
[Flag_ManagerCheckCost] [varchar](1) COLLATE Chinese_PRC_CI_AS NULL,  
CONSTRAINT [PK_COST_FEEINFO] PRIMARY KEY NONCLUSTERED  (     [Id] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] )   


CREATE TABLE [#customs_CustomsDeclaration](    
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [BusinessNum] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,     
 [AdvanceNum] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,     
 [DeclareDate] [datetime] NULL,     
 [DeliveryListNum] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,     
 [Declare_Name] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,  
 CONSTRAINT [PK_CUSTOMS_CUSTOMSDECLARATION] PRIMARY KEY NONCLUSTERED  (     [Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 
----------------------------------------------------------
insert into #bus_BusinessDetail(BusinessNum,Consignor) 
values ('RPO201310000001','A公司')
insert into #bus_BusinessDetail(BusinessNum,Consignor) 
values('RPI201310000015','B公司')
insert into #bus_BusinessDetail(BusinessNum,Consignor) 
values('RPI201310000016','A公司')
insert into #bus_BusinessDetail(BusinessNum,Consignor) 
values('BPO103942','A公司')
insert into #bus_BusinessDetail(BusinessNum,Consignor) 
values('YLO201310001539','A公司')
------------------------------------------------------------
insert into #cost_FeeInfo ([BusinessNum],[Flag_ManagerCheckCost])
values ('RPO201310000001','0')
insert into #cost_FeeInfo ([BusinessNum],[Flag_ManagerCheckCost])
values('RPO201310000001','0')
insert into #cost_FeeInfo ([BusinessNum],[Flag_ManagerCheckCost])
values('RPI201310000015','0' )
insert into #cost_FeeInfo ([BusinessNum],[Flag_ManagerCheckCost])
values('RPI201310000015','1' )
insert into #cost_FeeInfo ([BusinessNum],[Flag_ManagerCheckCost])
values('RPI201310000016','1')
insert into #cost_FeeInfo ([BusinessNum],[Flag_ManagerCheckCost])
values('RPI201310000016','1') 
------------------------------------------------------------
insert into #customs_CustomsDeclaration ([BusinessNum],[AdvanceNum],[DeliveryListNum],[DeclareDate],[Declare_Name])
values ('RPO201310000001',null,null,'2013-1-2',null)
insert into #customs_CustomsDeclaration ([BusinessNum],[AdvanceNum],[DeliveryListNum],[DeclareDate],[Declare_Name])
values('RPI201310000015','43545','34565','2013-1-5','reter')
insert into #customs_CustomsDeclaration ([BusinessNum],[AdvanceNum],[DeliveryListNum],[DeclareDate],[Declare_Name])
values ('RPI201310000016','123123','324324324','2013-2-1','ewrewr')
insert into #customs_CustomsDeclaration ([BusinessNum],[AdvanceNum],[DeliveryListNum],[DeclareDate],[Declare_Name])
values('BPO103942','4345','23432','2013-1-1','3432') 

insert into #customs_CustomsDeclaration ([BusinessNum],[AdvanceNum],[DeliveryListNum],[DeclareDate],[Declare_Name])
values('YLO201310001539','4345','23432','2013-1-1','3432') 

------------------------------------------------------------



;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_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
/*
select * from #cost_FeeInfo
select * from #bus_BusinessDetail
select * from #customs_CustomsDeclaration
*/
/*
财务 数据   
BusinessNum    Flag_ManagerCheckCost(是否审核1审核0未审核) 
RPO201310000001     0 
RPO201310000001     0 
RPI201310000015     1 
RPI201310000015     0 
RPI201310000016     1 
RPI201310000016     0   

业务数据 
BusinessNum       consignor 
RPO201310000001   A公司 
RPI201310000015   B公司 
RPI201310000016   A公司 
BPO103942         A公司   

报关数据 
BusinessNum       AdvanceNum        DeliveryListNum          DeclareDate           Declare_Name 
RPO201310000001   null                 null                   2013-1-2                null
RPI201310000015    123123              324324324              2013-2-1               ewrewr 
RPI201310000016     43545              34565                  2013-1-5                reter 
BPO103942           4345                 23432                2013-1-1                3432   
YLO201310001539    4345                  23432                2013-1-1                3432
我想查询出 已经审核 的 报关单 如果 费用 有 一条未审核 的 也算未审核              
未审核 的 报关单 也是 一样(但是要把为录入费用的也算未审核)   
想要 的 结果 是 
(这是查询 未审核的) 
BusinessNum       AdvanceNum  DeliveryListNum  DeclareDate  Declare_Name   IsExists（表示几条费用如果是0那么就是0） 
RPO201310000001    null         null            2013-1-2       null          2 
RPI201310000015    123123      324324324    2013-2-1           ewrewr        2 
BPO103942            4345       23432           2013-1-1        3432         0 
YLO201310001539    4345         23432           2013-1-1        3432         0   
 
(已审核的) 
BusinessNum       AdvanceNum  DeliveryListNum  DeclareDate  Declare_Name   IsExists（表示几条费用如果是0那么就是0） 
RPI201310000016     43545      34565            2013-1-5        reter         1   
(全部的) 
RPO201310000001    null         null            2013-1-2       null          2 
RPI201310000015    123123      324324324        2013-2-1       ewrewr        2 
RPI201310000016     43545      34565            2013-1-5        reter         2 
BPO103942            4345       23432           2013-1-1        3432         0
YLO201310001539    4345         23432           2013-1-1        3432          0
*/
if OBJECT_ID('tempdb.dbo.#bus_BusinessDetail') is not null drop table #bus_BusinessDetail
if OBJECT_ID('tempdb.dbo.#cost_FeeInfo') is not null drop table #cost_FeeInfo
if OBJECT_ID('tempdb.dbo.#customs_CustomsDeclaration') is not null drop table #customs_CustomsDeclaration

如上 sql 语句 和问题 求高手 解答
这是 上一篇 帖子http://bbs.csdn.net/topics/390628319
[解决办法]

;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
*/

[解决办法]

想帮你写，问题我看不懂你想要什么。

是这样的高手 ， 查已审核的 报关单 的时候 必须是 这票的所有 费用 都已经审核了
 未审核的 包括 未录入费用的报关单 和 未审核的费用(如果费用中 有审核的和未审核的那么这票也算未审核)
全部 就是 把所有报关单 都 查到

但RPI201310000016 有一条数据是0的，也就是未审核，那为什么在最后的结果中，也是显示为 已经审核呢
高手 财务 数据 那 我给 错了 请 详细 看看 一下 insert into  的时候 的 值 谢谢
谢谢 帮助


是这样不，你看看：

;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
*/

查看更多 下一篇