首页 新闻 赞助 找找看

Sql不会写

0
悬赏园豆:5 [已解决问题] 解决于 2016-06-04 22:35
SELECT 
(SDMS_OrganiseUnit.OrganiseUnitName), 
COUNT(FMDS_Water_DikeProject.OrganiseUnitName)as shu,
sum(FMDS_Water_DikeProject.NowDikeLength) as NowDikeLength,
(sum(NowAverageIncrease)/COUNT(FMDS_Water_DikeProject.OrganiseUnitName)) as NowAverageIncrease,
(sum(NowCrestWidth)/COUNT(FMDS_Water_DikeProject.OrganiseUnitName)) as NowCrestWidth,
COUNT(FMDS_Water_NewReservoir.OrganiseUnitName)as GeShu,
sum(SIrrTotal) as SIrrTotal,
  
--(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(t.Swsl) as Swsl,
sum(t1.Bs) as bs,
sum(t2.sz) as sz,
sum(t3.Swgcd) as Swgcd,
sum(t4.Total)/10000 as toatl,
sum(t5.Total1) as toatl1,
sum(ControlArea) as ControlArea,
(sum(FMDS_Water_NewReservoir.CompletedTotal)+ sum(FMDS_Water_NewReservoir.UnfinishedTotal))
as CompletedTotal
 
  
FROM SDMS_OrganiseUnit 
 
left join FMDS_Water_NewReservoir 
on SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_NewReservoir.OrganiseUnitID and FMDS_Water_NewReservoir.IsDelete=0
  
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

因为又加了一个左链接,前面的数据是2,把后面的也给整成2le,结果数据加倍了,怎么整?

哪位大哥能远程遥控我一下,或者给个解决办法

QQ:1658026873

 

总表:关联OrganiseUnitID,OrganiseUnitName
OrganiseUnitName
(总表的查询条件:
WHERE OrganiseUnitModel=1 AND OrganiseUnitType=3
AND SDMS_OrganiseUnit.IsDelete=0
group by SDMS_OrganiseUnit.OrganiseUnitName,DisplayIndex
ORDER BY DisplayIndex)


isdelete(是否删除)

 

水库表(需要字段和)
FMDS_Water_NewReservoir
字段:
CompletedTotal 完成
UnfinishedTotal 完成(需要他俩的和)
ControlArea控制面积
需要数量(就是有几个重复的值)


堤防工程表(需要字段和)
需要数量(就是有几个重复的值)
FMDS_Water_DikeProject
字段:
NowDikeLength 堤长

堤防工程表的字表(关联字段mainID)
FMDS_Water_DikeProject_Backwater
需要数量(就是有几个重复的值)

 

堤防工程表的字表(关联字段mainID)
FMDS_Water_Quantities
需要TotalALL的数量和

 


堤防工程表的字表(关联字段mainID)
FMDS_Water_DikeProject_Buildings
需要数量(就是有几个重复的值)
条件:where FMDS_Water_DikeProject_Buildings.Type=2 and isdelete=0

。。。。。。。以此类推,后面还要添加连接。。。。

博学多思的主页 博学多思 | 初学一级 | 园豆:76
提问于:2015-10-16 12:54
< >
分享
最佳答案
0

........................

收获园豆:5
✎﹏ℳ๓₯㎕ღ | 小虾三级 |园豆:1499 | 2015-10-16 14:29
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册