关于特定规则排序问题

zhx730621 2019-04-20 11:54:20

SELECT jlxcyf, jlxccjks, SUM(CASE WHEN wtclsbwg = '1' THEN 1 ELSE 0 END) AS waiguan,
SUM(CASE WHEN wtclsbbz = '1' THEN 1 ELSE 0 END) AS biaozhi,
SUM(CASE WHEN wtclsbxs = '1' THEN 1 ELSE 0 END) AS xianshi,
SUM(CASE WHEN wtclsbcf = '1' THEN 1 ELSE 0 END) AS cunfang,
SUM(CASE WHEN wtclsbzj = '1' THEN 1 ELSE 0 END) AS zijiao,
SUM(CASE WHEN wtclsbcz = '1' THEN 1 ELSE 0 END) AS caozuo
FROM jlxcjltz
WHERE (jlxcdbh LIKE '%-%') AND (jlxcyf = '2019-3')
GROUP BY jlxcyf, jlxccjks
ORDER BY jlxccjks

假如有如上数据

现要求显示的结果按照jlxccjks字段特定顺序排序

比如按冲压车间，焊装一车间，焊装二车间，涂装车间，总装车间，整车品质管理室，压铸车间，机加车间，装配车间，发动机品质管理室，设备保全室这样来显示有什么办法吗？

...全文

167 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

Dear SQL(燊) 2019-04-22

打赏
举报

引用 4 楼 Dear SQL 的回复:

建张辅助表用于排序如：list


;
with list as(
select id=1,jlxccjks='焊装一车间' union all 
select id=2,jlxccjks='焊装二车间' union all
select id=3,jlxccjks='涂装车间' union all
select id=4,jlxccjks='总装车间' union all
select id=5,jlxccjks='整车品质管理室' union all
select id=6,jlxccjks='压铸车间' union all
select id=7,jlxccjks='机加车间' union all
select id=8,jlxccjks='装配车间' union all
select id=9,jlxccjks='发动机品质管理室' union all
select id=10,jlxccjks='设备保全室'

)

SELECT a.jlxcyf, jlxccjks, SUM(CASE WHEN wtclsbwg = '1' THEN 1 ELSE 0 END) AS waiguan, 
      SUM(CASE WHEN wtclsbbz = '1' THEN 1 ELSE 0 END) AS biaozhi, 
      SUM(CASE WHEN wtclsbxs = '1' THEN 1 ELSE 0 END) AS xianshi, 
      SUM(CASE WHEN wtclsbcf = '1' THEN 1 ELSE 0 END) AS cunfang, 
      SUM(CASE WHEN wtclsbzj = '1' THEN 1 ELSE 0 END) AS zijiao, 
      SUM(CASE WHEN wtclsbcz = '1' THEN 1 ELSE 0 END) AS caozuo
FROM jlxcjltz a
inner join list b on a.jlxccjks=b.jlxccjks
WHERE (a.jlxcdbh LIKE '%-%') AND (a.jlxcyf = '2019-3')
GROUP BY jlxcyf, a.jlxccjks
ORDER BY b.id

数据量大的话建议汇总扣再关联辅助表

Dear SQL(燊) 2019-04-22

打赏
举报

建张辅助表用于排序如：list


;
with list as(
select id=1,jlxccjks='焊装一车间' union all 
select id=2,jlxccjks='焊装二车间' union all
select id=3,jlxccjks='涂装车间' union all
select id=4,jlxccjks='总装车间' union all
select id=5,jlxccjks='整车品质管理室' union all
select id=6,jlxccjks='压铸车间' union all
select id=7,jlxccjks='机加车间' union all
select id=8,jlxccjks='装配车间' union all
select id=9,jlxccjks='发动机品质管理室' union all
select id=10,jlxccjks='设备保全室'

)

SELECT a.jlxcyf, jlxccjks, SUM(CASE WHEN wtclsbwg = '1' THEN 1 ELSE 0 END) AS waiguan, 
      SUM(CASE WHEN wtclsbbz = '1' THEN 1 ELSE 0 END) AS biaozhi, 
      SUM(CASE WHEN wtclsbxs = '1' THEN 1 ELSE 0 END) AS xianshi, 
      SUM(CASE WHEN wtclsbcf = '1' THEN 1 ELSE 0 END) AS cunfang, 
      SUM(CASE WHEN wtclsbzj = '1' THEN 1 ELSE 0 END) AS zijiao, 
      SUM(CASE WHEN wtclsbcz = '1' THEN 1 ELSE 0 END) AS caozuo
FROM jlxcjltz a
inner join list b on a.jlxccjks=b.jlxccjks
WHERE (a.jlxcdbh LIKE '%-%') AND (a.jlxcyf = '2019-3')
GROUP BY jlxcyf, a.jlxccjks
ORDER BY b.id

二月十六 2019-04-22

打赏
举报

order by 后边加个case when

SELECT jlxcyf, jlxccjks, SUM(CASE WHEN wtclsbwg = '1' THEN 1 ELSE 0 END) AS waiguan, 

      SUM(CASE WHEN wtclsbbz = '1' THEN 1 ELSE 0 END) AS biaozhi, 

      SUM(CASE WHEN wtclsbxs = '1' THEN 1 ELSE 0 END) AS xianshi, 

      SUM(CASE WHEN wtclsbcf = '1' THEN 1 ELSE 0 END) AS cunfang, 

      SUM(CASE WHEN wtclsbzj = '1' THEN 1 ELSE 0 END) AS zijiao, 

      SUM(CASE WHEN wtclsbcz = '1' THEN 1 ELSE 0 END) AS caozuo

FROM jlxcjltz

WHERE (jlxcdbh LIKE '%-%') AND (jlxcyf = '2019-3')

GROUP BY jlxcyf, jlxccjks

ORDER BY CASE WHEN jlxccjks='冲压车间' THEN 1

WHEN  jlxccjks='焊装一车间' THEN 2

WHEN jlxccjks='焊装二车间' THEN 3

......  --把所有情况都列举全