差一个分组或者去重，不知道怎么写？

 
 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