sql 和mysql常用函数--posgre
1. case, count sql (mysql)
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
用一个表的数据，批量更新另一个表，有关联条件
(mysql)
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 )
(posgre sql)
UPDATE dm.schoolentityexamine t1
SET areacode = t2.gatherorganizationno
from dm.schoolbusinessrelationexamine t2
where t1.organizationid =t2.organizationid
UPDATE dm.schoolentityexamine t1
SET shengmc = t2.gatherregiona,
shimc = t2.gatherregionb,
qumc = t2.gatherregionc
from dm.schoolbusinessrelationexamine t2
where t1.organizationid =t2.organizationid
我写的一个view
SELECT t1.sheng, t1.shengmc, t1.shi, t1.shimc, t1.qu, t1.qumc, t1.qxs, t1.ddztm, t2.ddztm ddztm2, t3.sqzndcwj, ( CASE WHEN t1.ddztm = 1 AND t2.ddztm = 1 AND t3.sqzndcwj = 1 THEN 1 ELSE 0 END ) wcs, ( CASE WHEN t1.ddztm = 1 AND t2.ddztm = 1 AND t3.sqzndcwj = 1 THEN 1 ELSE 0 END ) _pb_wcjd FROM dim.dim_sqznrxsxclyq t1 LEFT JOIN dim.dim_sqznxsjdqk t2 ON t1.qu = t2.qu LEFT JOIN uc.uc_qu t3 ON t1.qu = t3.qu SELECT t1.sheng, t1.shengmc, t1.shi, t1.shimc, t1.qu, t1.qumc, t1.qxs, ( CASE WHEN t1.ddztm = 1 AND t2.ddztm = 1 AND t4.sqzndcwj = 1 THEN 1 ELSE 0 END ) wcs, jzzsfyq+t1.jzzmsfyq+t1.jyzmsfyq+t1.sbzmsfyq+t1.jhgxzmsfyq+t1.xqjywczmsfyq+t1.yfjzzmsfyq+t1.jhsyzmsfyq+t1.hjdwrjhzmsfyq+t1.cgzcrxnlzm cls, t2.ywjyxszs, t2.gbxxzxxsrs, t2.mbxxzxxsrs, t2.mbxxzxxsrs/t2.ywjyxszs, t2.sqznzs, t2.jcwgrysqzns, t2.jcwgrysqznjdgbxxrs, t2.jcwgrysqznjdmbxxrs, t2.jcwgrysqznxszfgmmbxxxwrs, t2.jcwgrysqznjdgbxxrs+t2.jcwgrysqznxszfgmmbxxxwrs/t2.jcwgrysqzns, t2.xynsfsslgmxwzc, (CASE WHEN t2.xynsfsslgmxwzc=0 then 1 else 0 END) xynsfsslgmxwzc_no, t2.gmxwfybzxx, t2.gmxwfybzcz, t2.gmxwsffgsysqzn, (CASE WHEN t2.gmxwsffgsysqzn=0 then 1 else 0 END) gmxwsffgsysqzn_no, t2.gmxwtj, t3.t4, (CASE WHEN t3.t4=0 then 1 else 0 END) t4_no, t3.t5_1, t3.t6_1, t3.t7_1, t3.t8_1, (CASE WHEN t1=0 then '线上办理' WHEN t1=1 then '线上、线下相结合' WHEN t1=2 then '线下办理' END) str1, CONCAT_WS(';', (CASE WHEN t1.jzzsfyq=1 then CONCAT('居住证(',t1.jzzyqmjgy,')') END) , (CASE WHEN t1.jzzmsfyq=1 then CONCAT('居住证明(',t1.jzzmyqmjgy,')') END) , (CASE WHEN t1.jyzmsfyq=1 then CONCAT('就业证明(',t1.jyzmyqmjgy,')') END) , (CASE WHEN t1.sbzmsfyq=1 then CONCAT('社保证明(',t1.sbzmyqmjgy,')') END) , (CASE WHEN t1.jzzsfyq=1 then '监护关系证明' END) , (CASE WHEN t1.xqjywczmsfyq=1 then '学前教育完成证明' END) , (CASE WHEN t1.yfjzzmsfyq=1 then '预防接种证明' END) , (CASE WHEN t1.jhsyzmsfyq=1 then '计划生育证明' END) , (CASE WHEN t1.hjdwrjhzmsfyq=1 then '户籍地无人监护证明' END) , (CASE WHEN t1.cgzcrxnlzm=1 then '超过正常入学年龄证明' END) ) str2 FROM dim.dim_sqznrxsxclyq t1 LEFT JOIN dim.dim_sqznxsjdqk t2 ON t1.qu = t2.qu LEFT JOIN dim.dim_xwjg t3 ON t1.qu = t3.qu LEFT JOIN uc.uc_qu t4 ON t1.qu = t4.qu where t1.qu like '1101%'
查看视图view的sql
select * from INFORMATION_SCHEMA.views where table_name = 'v_uc_jigou_tslx_tbjd'
查重
select CONCAT(xqid,'-',njbm) xqnjbm from bs.bs_xiaoqu_nianji group by xqnjbm having count(xqnjbm) > 1
exists:
SELECT Websites.name, Websites.url FROM Websites WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200); SELECT Websites.name, Websites.url FROM Websites WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);
IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
SELECT IFNULL(NULL, "RUNOOB"); 以上实例输出结果为：RUNOOB
SELECT CHAR_LENGTH('我爱祖国'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','祖国'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱祖国',1,2,'非常爱'); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('select'); /*小写*/
SELECT UPPER('select'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('我想吃包子','包子','烧麦'); /*替换字符串*/
SELECT SUBSTR('我想吃包子',4,6); /*截取字符串,开始和长度*/ 从1开始，不是从0开始
SELECT REVERSE('我想吃包子'); /*反转
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());实例
update dim.dim_kcbx_yscx t1 left join dim.dim_kcbx_yscx_hsrecord t2 on t1.id=t2.pid set sfsc='0' where t2.pid is not null
