首页 新闻 搜索 专区 学院

下面sql 语句 怎么用group by将相同的数据合并?

0
悬赏园豆:30 [已解决问题] 解决于 2016-06-02 18:18

select B.EQ_IMPORTANTPARTLCMID, D.SORT_NAME,
c.equipment_name,
A.KEYSPARENAME ,
decode(A.ENGINELECTRICSORTCODE,'1','机械','2','电气','3','其他')ENGINELECTRICSORTCODE,
A.LIFECYCLE,
C.site_code, E.zcs,RWWCL,
E.YWC,E.WWC,E.YYH,WYH,WJCYH,
decode( A.taskexecstatus,'0','未完成','1','已完成') as taskexecstatus
from T_EQ_IMPORTANTPARTLCM A
left join T_EQ_IMPORTANTPARTLCMTASK B on A.EQ_IMPORTANTPARTLCMID=b.eq_importantpartlcmid
left join T_EQ_BA_EQUIPMENT C on A.eq_ba_equipment_id=C.t_eq_ba_equipment_id
left join T_EQ_BA_EQUIPMENT_SORT D on D.t_eq_ba_equipment_sort_id=C.section_id
left join (select t.eq_importantpartlcmid ,
count(t.eq_importantpartlcmid) zcs,
round((count(t.CHECKTIME)/count(t.eq_importantpartlcmid))*100,2) RWWCL,
count(t.CHECKTIME) YWC,
m.WWC, count(t.eq_importantpartlcmid)-E.WYH as WJCYH ,
count(t.eq_importantpartlcmtaskid) YYH,
E.WYH
from T_EQ_IMPORTANTPARTLCMTASK t,
(select eq_importantpartlcmid, count(*) WWC from T_EQ_IMPORTANTPARTLCMTASK where CHECKTIME is null group by eq_importantpartlcmid ) m,
(select eq_importantpartlcmid, count(*) WYH from T_EQ_IMPORTANTPARTLCMTASK where FOUNDHIDDENTIME is null group by eq_importantpartlcmid ) E
where t.eq_importantpartlcmid=e.eq_importantpartlcmid and e.eq_importantpartlcmid=t.eq_importantpartlcmid
group by t.eq_importantpartlcmid,m.WWC,E.WYH) E on e.eq_importantpartlcmid=A.eq_importantpartlcmid where B.EQ_IMPORTANTPARTLCMID is not null

 以上是sql语句,现在需要按照sql数据图c.equipment_name和A.KEYSPARENAME 两个字段分组要怎么写

m869485074的主页 m869485074 | 初学一级 | 园豆:84
提问于:2016-05-31 15:24
< >
分享
最佳答案
1

如果是Oracle的话,你可以用分析函数试试

提示一下:row_number() over(partition by xxx )  

举例,根据部门分组,并按工资排序

select e.* , row_number() over(partition by deptno order by sal ) rn from emp e;

得到每个部门工资最高的两个员工信息:

select * from (select e.*, row_number() over(partition by deptno order by sal desc)rn  from emp e) where rn <=2

希望这么举例,能帮到你。

收获园豆:30
半夜起来敲代码 | 菜鸟二级 |园豆:257 | 2016-06-02 18:10

虽然已经解决了,不过很谢谢你的回答。分给你了

m869485074 | 园豆:84 (初学一级) | 2016-06-02 18:17
其他回答(1)
0

你的意思是根据c.equipment_name和A.KEYSPARENAME两个字段 去重?

laugher_ccc | 园豆:593 (小虾三级) | 2016-05-31 15:36

差不多这意思,假如以上sql查出的数据为第一条1 2 3 4 5 6,第二条1 2 3  4 5 6。1 2为c.equipment_name、A.KEYSPARENAME,现在要将两条数据合为一条 用 group by 怎么写

支持(0) 反对(0) m869485074 | 园豆:84 (初学一级) | 2016-05-31 15:43
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册