1. case, count sql
SELECT (case when shi='1101' then 1 when shi='2101' then 2 when shi='3101' then 3 when shi='3206' then 4 when shi='5101' then 5 when shi='4101' then 6 when shi='1404' then 7 when shi='3710' then 8 when shi='4401' then 9 end) sortc, sheng 省代码, shengmc 省名称, shi 市代码, shimc 市名称, cast(sum( qxs ) AS UNSIGNED) 填报数, cast(sum( saved ) AS UNSIGNED) 完成数, cast(sum( zj ) AS UNSIGNED) 总计, cast(sum( zzyy ) AS UNSIGNED) 正在营业, cast(sum( ztyy ) AS UNSIGNED) 暂停营业, cast(sum( zx ) AS UNSIGNED) 转型, cast(sum( qt ) AS UNSIGNED) 其他, concat(ROUND((sum(ztyy)/sum(zj))*100,2),'%') 暂停率, concat(ROUND(((sum(zx)+sum(qt))/sum(zj))*100,2),'%') 压减率, cast(sum(yzfjgs) AS UNSIGNED) 营转非机构数(个), cast(sum(zxzfzdjjgs) AS UNSIGNED) 执行政府指导价机构数(个), cast(sum(syfwhtjgs) AS UNSIGNED) 使用《中小学生校外培训服务合同(示范文本)》(2021年修订版)机构(个), cast(sum(zdxs) AS UNSIGNED) 在读学生(人), ROUND(sum(wxfje),2) 未消课金额(万元), ROUND(sum(qx),2) 欠薪(万元), cast(sum(zjjgjgyzhzt) AS UNSIGNED) 监管函正在营业和暂停, cast(sum(zjjgjgzx) AS UNSIGNED) 监管函注销监管机构, sum(zjjgjeyzhzt) 监管函正在营业和暂停1, sum(zjjgjezx) 监管函注销监管机构1, cast(sum(jzgzj) AS UNSIGNED)总计1, cast(sum(jnsb) AS UNSIGNED)其中缴纳社保, cast(sum(jxry) AS UNSIGNED)教学教研人员, cast(sum(qtcyrys) AS UNSIGNED)其他从业人员, cast(sum(ycy) AS UNSIGNED)已裁员, cast(sum(yjcy) AS UNSIGNED)预计裁员 FROM dim.dim_xxpxjgqktj_qu WHERE shi='1101' or shi='3101' or shi='4101' or shi='2101' or shi='4401' or shi='1404' or shi='5101' or shi='3206' or shi='3710' GROUP BY sheng,shengmc,shi,shimc order by sortc
用一个表的数据,批量更新另一个表,有关联条件
UPDATE uc.uc_jigou_nianji t1 INNER JOIN ( SELECT jgdm, grade_1,grade_2,grade_3,grade_4,grade_5,grade_6 FROM dim.dim_jzwjxx_2021_termend_v1 ) t2 ON t1.SCHOOLID = t2.jgdm SET t1.shfs_jzwj_2021termend = ( CASE WHEN t1.nj = '1' THEN t2.grade_1 WHEN t1.nj = '2' THEN t2.grade_2 WHEN t1.nj = '3' THEN t2.grade_3 WHEN t1.nj = '4' THEN t2.grade_4 WHEN t1.nj = '5' THEN t2.grade_5 WHEN t1.nj = '6' THEN t2.grade_6 else 0 end )