首页 新闻 搜索 专区 学院

求救。sql不会写

0
[已解决问题] 解决于 2016-06-04 22:36
差一个分组或者去重,不知道怎么写?
 
 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 

博学多思的主页 博学多思 | 初学一级 | 园豆:87
提问于:2015-10-23 09:33
< >
分享
最佳答案
0

如果主表数据上万,不知道你这查询要多少时间

奖励园豆:5
Kerwin1202 | 初学一级 |园豆:68 | 2015-10-23 09:44

主表数据很少,不知道怎么驱虫现在

博学多思 | 园豆:87 (初学一级) | 2015-10-23 09:46
其他回答(2)
0

 使用row_number

悦光阴 | 园豆:2239 (老鸟四级) | 2015-10-23 14:34
0

你这结果又重么?我怎么没看出来结果有重呀~~

三个小圈圈 | 园豆:206 (菜鸟二级) | 2015-10-23 17:27
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册