求高手 一条 sql语句

Question

我 有 三 个表 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) 求高手 解答 ，我 写了 好久都没 写出来 

Answer 1

给出三个表的完整结构，
cost_FeeInfo(财务) 这个表哪个字段表示存在费用？

Answer 2

就是 根据业务号 BusinessNum  去 财务 表 查询 
select count（1） from cost_FeeInfo cfi where cfi.BusinessNum =  业务表 的或是 报关表 的

Answer 3

业务表 数据BusinessNum        consignor
000
0000
00000              NULL
000254              561
111 报关单 数据
BusinessNum     AdvanceNum,DeliveryListNum,DeclareDate,Declare_Name ,Flag_Customs
bgI201309000004        2013-09-23 00:00:00.000 1
RPI201309000001        2013-09-23 00:00:00.000 1
bgO201309000001        2013-09-23 00:00:00.000 0
bgI201309000005        2013-09-23 00:00:00.000 1
bgI201309000006        2013-09-23 00:00:00.000 1
财务数据
BusinessNum
bgI201309000008
bgI201309000008
bgI201309000008
bgI201309000008
bgI201309000008财务表 的 BusinessNum是外键
业务表 是 BusinessNum 主键
报关单 的 BusinessNum 是唯一键

Answer 4

财务 数据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我想查询出 已经审核 的 报关单 如果 费用 已经 审核 那么即使有 未审核 的 也算审核  
           未审核 的 报关单 也是 一样(但是要把为录入费用的也算未审核)想要 的 结果 是 (这是查询 未审核的)
BusinessNum       AdvanceNum  DeliveryListNum  DeclareDate  Declare_Name   IsExists（表示几条费用如果是0那么就是0）
RPO201310000001    null         null            2013-1-2       null          2
RPI201310000015    123123      324324324 2013-2-1       ewrewr        1
RPI201310000016     43545      34565           2013-1-5        reter         1
BPO103942            4345       23432           2013-1-1        3432         0(已审核的)
BusinessNum       AdvanceNum  DeliveryListNum  DeclareDate  Declare_Name   IsExists（表示几条费用如果是0那么就是0）
RPI201310000015    123123      324324324 2013-2-1       ewrewr        1
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

Answer 5

CREATE TABLE [dbo].[bus_BusinessDetail](
[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 [dbo].[cost_FeeInfo](
[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 [dbo].[customs_CustomsDeclaration](
[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] 

Answer 6

insert into bus_BusinessDetail values ('RPO201310000001','A公司')
insert into bus_BusinessDetail values ('RPI201310000015','B公司')
insert into bus_BusinessDetail values ('RPI201310000016','A公司')
insert into bus_BusinessDetail values ('BPO103942','A公司')
insert into cost_FeeInfo values ('RPO201310000001','0')
insert into cost_FeeInfo values ('RPO201310000001','0')insert into cost_FeeInfo values ('RPI201310000015','1' )
insert into cost_FeeInfo values ('RPI201310000015','0' )insert into cost_FeeInfo values ('RPI201310000016','1')
insert into cost_FeeInfo values ('RPI201310000016','0')insert into customs_CustomsDeclaration values ('RPO201310000001',null,null,getdate(),null)
insert into customs_CustomsDeclaration values ('RPI201310000016','43545','34565',getdate(),'reter')insert into customs_CustomsDeclaration values ('RPI201310000015','','',getdate(),'')
insert into customs_CustomsDeclaration values ('BPO103942','','',getdate(),'')

Answer 7

看你的要求，sql语句应该没有那么复杂select AdvanceNum,DeliveryListNum,Consignor,DeclareDate,Declare_Name,BusinessNum,Flag_Customs  
from customs_CustomsDeclaration a
left join 
(select b.BusinessNum,count(*) as totalNum,sum(case when isnull(f.Flag_ManagerCheckCost,'')='0' then 1 else 0 end) as checkNum 
from bus_BusinessDetail b 
left join cost_FeeInfo f on b.BusinessNum=f.BusinessNum
group by b.BusinessNum 
)b on a.BusinessNum=b.BusinessNum
where a.InputDate BETWEEN '2013-10-23 00:00:00' AND '2013-10-30 00:00:00' AND a.IsAvailable = '1' 
and b.checkNum=0

Answer 8

这些建表语句，几个表都没有ID列，什么看到主键列都是ID呢？

Answer 9

这些建表语句，几个表都没有ID列，什么看到主键列都是ID呢？
我把 ID  给 删除 了 只留有用 的列  每个表 都 有 70  多列 呢 

Answer 10

ALTER PROCEDURE [dbo].[bus_BusinessDetail_SelectByCost]
       @DeliveryListNum varchar(50),
       @Consignor varchar(100),
   @CompanyName varchar(50),   
     @OperatorMan VARCHAR(255),
       @StartInputDate DATETIME,  
       @EndInputDate DATETIME,    
     @BusinessNum varchar(20),  
      @AdvanceNum varchar(20),     
          @ApproveNum varchar(20),     
         @VoyageNum  varchar(50),
       @TotalNum varchar(12),     
       @DocumentNum varchar(50),      
       @ContainerNum varchar(50),      
    @Flag_Customs varchar(1),   
  @Flag_ManagerCheckCost varchar(1)     
AS
      BEGIN   
    DECLARE @SQL VARCHAR(8000)
      SET @SQL = ' SELECT AdvanceNum,DeliveryListNum,Consignor,DeclareDate,Declare_Name,bbd.BusinessNum
                                 ,Flag_Customs                        
                        from  customs_CustomsDeclaration as ccd ,bus_BusinessDetail as bbd   '   IF  @Flag_ManagerCheckCost = '0'   
         BEGIN      
          SET @SQL = @SQL + ' where bbd.BusinessNum = ccd.BusinessNum and bbd.BusinessNum in  
                   (  select BusinessNum from (select b.BusinessNum,count(*) as totalNum,  
                   SUM(case when f.Flag_ManagerCheckCost= '''+@Flag_ManagerCheckCost+''' 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 ''' + CONVERT(VARCHAR(50),@StartInputDate,120) + 
          ''' AND ''' + CONVERT(VARCHAR(50),@EndInputDate,120) + '''  group by BusinessNum )  '     
         END 
     ELSE IF @Flag_ManagerCheckCost = '1'  
             BEGIN        
                  SET @SQL = @SQL + ' where bbd.BusinessNum = ccd.BusinessNum  
  and bbd.BusinessNum in  (  select BusinessNum from  
                                                               (select b.BusinessNum,count(*) as totalNum,SUM(case when f.Flag_ManagerCheckCost= '''+@Flag_ManagerCheckCost+''' 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=checkNum and totalNum>0 )'        
             END  
     ELSE     
BEGIN       
            SET @SQL = @SQL + ' where bbd.BusinessNum = ccd.BusinessNum  '      
        END                 IF @DeliveryListNum IS NOT NULL  
      BEGIN       
       SET @SQL = @SQL + ' AND ccd.DeliveryListNum = '''+@DeliveryListNum+''''    
     END        IF @Consignor IS NOT NULL    
     BEGIN       
      SET @SQL = @SQL + ' AND bbd.Consignor = '''+@Consignor+''''     
     END     
 ELSE      
        BEGIN                IF @OperatorMan IS NOT NULL       
       BEGIN              
             SET @SQL = @SQL + ' AND bbd.Consignor IN (select CompanyName from basis_BusinessCompany where OperatorMan like ''%'+@OperatorMan+';%'')'               
       END        
    END               IF @StartInputDate IS NOT NULL AND @EndInputDate IS NOT NULL    
 BEGIN    
      SET @SQL = @SQL + ' AND ccd.InputDate BETWEEN ''' + CONVERT(VARCHAR(50),@StartInputDate,120) +                                ''' AND ''' + CONVERT(VARCHAR(50),@EndInputDate,120) + ''''      
      END      IF  @AdvanceNum IS NOT NULL     
      BEGIN       
SET @SQL = @SQL + ' AND ccd.AdvanceNum = ''' +@AdvanceNum + ''''    
       END         IF  @ApproveNum IS NOT NULL       
      BEGIN      
          SET @SQL = @SQL + ' AND ccd.ApproveNum = ''' +@ApproveNum+ ''''    
       END         IF @VoyageNum IS NOT NULL     
    BEGIN      
       SET @SQL = @SQL + ' AND ccd.VoyageNum = ''' +@VoyageNum+''''       
      END        IF @TotalNum IS NOT NULL     
      BEGIN        
      SET @SQL = @SQL + ' AND ccd.TotalNum = '''+@TotalNum+''''    
      END              IF @ContainerNum IS NOT NULL      
  BEGIN      
      SET @SQL = @SQL + ' AND ccd.TotalNum IN (SELECT TotalNum FROM customs_Container WHERE C_ContainerNum = ''' + @ContainerNum + ''')'      
        END                 IF @DocumentNum IS NOT NULL         
      BEGIN                       
       SET @SQL = @SQL + ' AND ccd.TotalNum IN (SELECT TotalNum FROM customs_Document WHERE (DocumentsCode = ''A'' OR DocumentsCode = ''B'') AND DocumentsNum = ''' + @DocumentNum + ''')'   
 END         IF @Flag_Customs IS NOT NULL      
  BEGIN          
      SET @SQL = @SQL + ' AND ccd.Flag_Customs = '''+@Flag_Customs+''''       
END     
SET @SQL = @SQL + ' AND ccd.IsAvailable = ''1'' '   
SET @SQL = @SQL + ' group by AdvanceNum,DeliveryListNum,Consignor,DeclareDate,Declare_Name,bbd.BusinessNum,Flag_Customs '         
PRINT(@SQL)      
EXEC(@SQL)      
END 以上是 我 以前 的sql 可以查询 审核 , 未审核 和 全部 的 
但是 现在 要加 上 一个 录入费用的 数量 我 想了一上午 页面 想出来 求 高手 帮我 一下 谢谢 了 

Answer 11

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

Answer 12

求全部的、未审核的、审核的，直接在你的代码基础上修改了：;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/*
DataType BusinessNum AdvanceNum DeliveryListNum DeclareDate Declare_Name IsExists
全部数据 BPO103942 4345 23432 2013-01-01 00:00:00.000 3432 0
全部数据 RPI201310000015 43545 34565 2013-01-05 00:00:00.000 reter 2
全部数据 RPI201310000016 123123 324324324 2013-02-01 00:00:00.000 ewrewr 2
全部数据 RPO201310000001 NULL NULL 2013-01-02 00:00:00.000 NULL 2
全部数据 YLO201310001539 4345 23432 2013-01-01 00:00:00.000 3432 0
未审核 BPO103942 4345 23432 2013-01-01 00:00:00.000 3432 0
未审核 RPI201310000015 43545 34565 2013-01-05 00:00:00.000 reter 2
未审核 RPO201310000001 NULL NULL 2013-01-02 00:00:00.000 NULL 2
未审核 YLO201310001539 4345 23432 2013-01-01 00:00:00.000 3432 0
已审核 RPI201310000016 123123 324324324 2013-02-01 00:00:00.000 ewrewr 2
*/