• sql


    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
    )
  • 相关阅读:
    技术人员的找工之路(1
    技术人员的找工之路(3)
    Endian的由来
    android平台开发笔记1Spinner不能在sub activity中使用
    谈谈Groupon的成功
    线程安全的同步读写类的模板设计
    项目管理文件package.json
    10个每个开发人员都应该知道的强大JavaScript 解构技术
    绿色下载站上线了(MVC +Telerik开源控件)
    我开发的新浪微博应用“微词典”通过审核,欢迎朋友们试用,多多建议!
  • 原文地址:https://www.cnblogs.com/lxgbky/p/15745298.html
Copyright © 2020-2023  润新知