none
求救,sql怎么写? RRS feed

  • 问题

  •  select * from (
      
      
      
      
     select * from(
    SELECT top 1000
    (SDMS_OrganiseUnit.OrganiseUnitName), 
    COUNT(isnull(FMDS_Water_DikeProject.OrganiseUnitName,0))as shu,
    sum(isnull(FMDS_Water_DikeProject.NowDikeLength,0)) as NowDikeLength,
    isnull((sum(NowAverageIncrease)/COUNT(FMDS_Water_DikeProject.OrganiseUnitName)),0) as NowAverageIncrease,
    isnull((sum(NowCrestWidth)/COUNT(FMDS_Water_DikeProject.OrganiseUnitName)),0) as NowCrestWidth,
      
      
       
    --(select Count(FMDS_Water_DikeProject_Backwater.Name) from FMDS_Water_DikeProject_Backwater
     --where  FMDS_Water_DikeProject_Backwater.MainID=FMDS_Water_DikeProject.ID and isdelete=0)  as Swsl
    sum(isnull( t.Swsl,0)) as hus,
    sum(isnull(t1.Bs,0))as bzs,
    sum(isnull(t2.sz,0))as szs,
    sum(isnull(t3.Swgcd,0)) as swgcd,
    sum(isnull( t4.Total,0))/10000 as gcl,
    sum(isnull(t5.Total1,0)) as wctz,
    0 as shu1,
    0 as ControlArea,
    0 as SFloodStorage
     
     
      
      
      
       
    FROM SDMS_OrganiseUnit 
      
      
       
    left join FMDS_Water_DikeProject 
    on SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_DikeProject.OrganiseUnitID and FMDS_Water_DikeProject.IsDelete=0
       
      
      
      
    left join
    (
    select MainID,Count(FMDS_Water_DikeProject_Backwater.Name) as Swsl
    from FMDS_Water_DikeProject_Backwater 
    where  isdelete=0
    group by FMDS_Water_DikeProject_Backwater.MainID 
    )t  
    on t.MainID=FMDS_Water_DikeProject.ID 
      
      
     
     
      
      
      
    left join 
    (
      select MainID, Count(FMDS_Water_DikeProject_Buildings.Name) as Bs 
      from FMDS_Water_DikeProject_Buildings  
      where FMDS_Water_DikeProject_Buildings.Type=1 and  isdelete=0
      group by FMDS_Water_DikeProject_Buildings.MainID 
        
    )  t1
    on t1.MainID = FMDS_Water_DikeProject.ID
      
    left join 
    (
      select MainID, sum(FMDS_Water_Quantities.TotalALL) as Total 
      from FMDS_Water_Quantities  
      where    isdelete=0
      group by FMDS_Water_Quantities.MainID 
        
    )  t4
    on t4.MainID = FMDS_Water_DikeProject.ID
      
      
    left join 
    (
      select MainID, sum(FMDS_Water_Investment.TotalALL) as Total1 
      from FMDS_Water_Investment  
      where    isdelete=0
      group by FMDS_Water_Investment.MainID 
        
    )  t5
    on t5.MainID = FMDS_Water_DikeProject.ID
      
      
      
      
      
      
      
      
    left join 
    (
      select MainID, Count(FMDS_Water_DikeProject_Buildings.Name) as Sz 
      from FMDS_Water_DikeProject_Buildings  
      where FMDS_Water_DikeProject_Buildings.Type=2 and  isdelete=0
      group by FMDS_Water_DikeProject_Buildings.MainID 
        
    )  t2
    on t2.MainID=FMDS_Water_DikeProject.ID 
      
    left join 
    (
      select MainID, Count(FMDS_Water_DikeProject_Point.Name) as Swgcd 
      from FMDS_Water_DikeProject_Point  
      where  isdelete=0
      group by FMDS_Water_DikeProject_Point.MainID 
        
    )  t3
    on t3.MainID=FMDS_Water_DikeProject.ID 
      
      
      
      
       
       
    WHERE OrganiseUnitModel=1 AND OrganiseUnitType=3 
    AND SDMS_OrganiseUnit.IsDelete=0 
    group by SDMS_OrganiseUnit.OrganiseUnitName,DisplayIndex
    ORDER BY DisplayIndex) as a
     
    union 
    select * from(
     
    SELECT top 1000
    (SDMS_OrganiseUnit.OrganiseUnitName), 
    0 as shu,
    0 as NowDikeLength,
    0 as NowAverageIncrease,
    0 as NowCrestWidth,
    0 as hus,
    0 as bzs,
    0 as szs,
    0 as swgcd,
    0 as gcl,
    0 as wctz,
    COUNT(isnull(FMDS_Water_NewReservoir.OrganiseUnitName,0))as shu1,
    sum(isnull(FMDS_Water_NewReservoir.ControlArea,0)) as ControlArea,
    isnull((sum(SFloodStorage)),0) as SFloodStorage
     
     
     
       
    FROM SDMS_OrganiseUnit 
      
      
       
    left join FMDS_Water_NewReservoir 
    on SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_NewReservoir.OrganiseUnitID and FMDS_Water_NewReservoir.IsDelete=0
       
      
      
      
     
      
      
       
       
    WHERE OrganiseUnitModel=1 AND OrganiseUnitType=3 
    AND SDMS_OrganiseUnit.IsDelete=0 
    group by SDMS_OrganiseUnit.OrganiseUnitName,DisplayIndex
    ORDER BY DisplayIndex) b) c 

    差一个分组或者去重,不知道怎么写?


    please verify my account

    2015年10月23日 1:31

答案