mysql查询

1.示例1

查询1：

SELECT CASE main_xm_sam31 WHEN '02' THEN 2 ELSE 1 END AS SPDJ FROM SR_MAIN_BG A WHERE A.PK_SR_MAIN = '1162275825622593536' AND (SYS_SPDJ = 0 OR SYS_SPDJ = -1)

查询2：

SELECT
count(1) AS ncount,
(
CASE sys_sjbj
WHEN 0 THEN
'个人发布'
WHEN 1 THEN
'组织发布'
WHEN 2 THEN
'机构发布'
ELSE
'其他'
END
) AS fblx,
ifnull(
sum(CASE rlzt WHEN 2 THEN 1 ELSE 0 END),
0
) AS xyl,
ifnull(
ROUND(
sum(
TIMESTAMPDIFF(DAY, fbsj, rlsj)
) / sum(CASE rlzt WHEN 2 THEN 1 ELSE 0 END),
1
),
0
) AS pjrlsj
FROM
sr_xqfb
WHERE
fbrq LIKE '2019%'
GROUP BY
sys_sjbj

查询3：

SELECT
CASE WHEN mdjlx = '0301' THEN '人道救援' 
WHEN mdjlx = '2201' THEN '低收入家庭免费用电信息'
WHEN mdjlx = '2301' THEN '低收入家庭优待信息'
ELSE '暂定-农办厅专项救助' END AS mdjlx,
sys_xzqh
from sr_main_zdts

----------------------------------------------------------------------------

----------------------------------------------------------------------------

查4（接口的平均调用时间）

SELECT
count,
(zsj / count) AS pjdysj
FROM
(
SELECT
count(1) AS count,
ifnull(
sum(
TIMESTAMPDIFF(DAY, a.DTJRQ, b.RKSJ)
),
0
) AS zsj
FROM
dsr_hc_bm a,
dsr_hc_hy b
WHERE
a.dbmbh = 'dsr_hc_hy'
AND a.DTJRQ LIKE CONCAT('2019', '%')
AND b.RKSJ LIKE CONCAT('2019', '%')
AND a.dhcid = b.hyhcid
) c

查5：

//每个市的救助岗位数量
SELECT
count(1) AS xzqhjzgw,
(
SELECT
b.xjgmc
FROM
mz_xzjg b
WHERE
b.node_id = substr(a.uxnode, 1, 4)
) AS xjgmc,
substr(a.uxnode, 1, 4) AS xzqh
FROM
bd_user a
WHERE
a.UTYPE = 1
GROUP BY
substr(a.uxnode, 1, 4)

查6：

//审批时效

SELECT
d.xjgmc,
round(zsj / zsl, 1) AS spsx
FROM
(
SELECT
sum(
TIMESTAMPDIFF(DAY, jdjsprq, jspwcrq)
) AS zsj,
count(1) AS zsl,
substr(JXZJGBH, 1, 4) xzqh
FROM
sp_jl b
WHERE
b.jzt = 1
AND jdjsprq LIKE '2019%'
AND jspwcrq LIKE '2019%'
AND jspxmbm IN (
'db_jz',
'kn_jz',
'wb_jz',
'ls_jz'
)
GROUP BY
substr(JXZJGBH, 1, 4)
) c,
mz_xzjg d
WHERE
c.xzqh = d.node_id

查7

//根据身份证年龄计算
SELECT
count(1) AS count
FROM
bd_user
WHERE
DATE_FORMAT(NOW(), '%Y') - SUBSTRING(usfz, 7, 4) >=0
AND DATE_FORMAT(NOW(), '%Y') - SUBSTRING(usfz, 7, 4) <=18
AND UTYPE = 999

查8：

//需求发布数量、类型、平均认领时间
SELECT
count(1) AS ncount,
(
CASE sys_sjbj
WHEN 0 THEN
'个人发布'
WHEN 1 THEN
'组织发布'
WHEN 2 THEN
'机构发布'
ELSE
'其他'
END
) AS fblx,
ifnull(
sum(CASE rlzt WHEN 2 THEN 1 ELSE 0 END),
0
) AS xyl,
ifnull(
ROUND(
sum(
TIMESTAMPDIFF(DAY, fbsj, rlsj)
) / sum(CASE rlzt WHEN 2 THEN 1 ELSE 0 END),
1
),
0
) AS pjrlsj
FROM
sr_xqfb
WHERE
fbrq LIKE '2019%'
GROUP BY
sys_sjbj

-----------------------------------------------------------------------

UNION ：2个select的字段数量必须一致，字段类型一致，去重

UNION ALL：2个select的字段数量必须一致，字段类型一致，不去重

用默认的值代替需要的需要查询的数值，0 jzrs_kn,0 MDBBZ_kn,0 jzje_kn

-- 低保：救助人数、救助标准(MDBBZ)、救助金额
select xzqh,xjgmc,sum(jzrs_db) jzrs_db,sum(MDBBZ_db) MDBBZ_db,sum(jzje_db) jzje_db,sum(jzrs_kn) jzrs_kn,sum(MDBBZ_kn) MDBBZ_kn,sum(jzje_kn) jzje_kn FROM(
SELECT
substr(a.xzqh, 1, 4) AS xzqh,
(
SELECT
xjgmc
FROM
mz_xzjg c
WHERE
c.node_id = substr(a.xzqh, 1, 4)
) AS xjgmc,
sum(a.MXSRS) AS jzrs_db,
-- MAX(a.MDBBZ) AS MDBBZ,
a.MDBBZ AS MDBBZ_db,
sum(a.MZJZJE) AS jzje_db,0 jzrs_kn,0 MDBBZ_kn,0 jzje_kn
FROM
sr_main_da a,
mz_xzjg_da b
WHERE
a.xzqh = b.xzqh_id
AND a.SYS_SCBJ = 0
AND a.SYS_SPZT = 1
AND a.SYS_DJZT = 1
AND a.sjbfnf = '2019'
AND a.sjbfyf = '07'
AND a.mdjlx = 'db_jz'
GROUP BY substr(a.xzqh, 1, 4)

UNION ALL
-- 低边：救助人数、救助标准(MDBBZ)、救助金额
SELECT
substr(a.xzqh, 1, 4) AS xzqh,
(
SELECT
xjgmc
FROM
mz_xzjg c
WHERE
c.node_id = substr(a.xzqh, 1, 4)
) AS xjgmc,
0 jzrs_db,0 MDBBZ_db,0 jzje_db,
sum(a.MXSRS) AS jzrs_kn,
-- MAX(a.MDBBZ) AS MDBBZ,
a.MDBBZ AS MDBBZ_kn,
sum(a.MZJZJE) AS jzje_kn
FROM
sr_main_da a,
mz_xzjg_da b
WHERE
a.xzqh = b.xzqh_id
AND a.SYS_SCBJ = 0
AND a.SYS_SPZT = 1
AND a.SYS_DJZT = 1
AND a.sjbfnf = '2019'
AND a.sjbfyf = '07'
AND a.mdjlx = 'kn_jz'
GROUP BY
substr(a.xzqh, 1, 4)
) e group by xzqh

---------------------------------------------------

case when 作查询条件

SELECT
*
FROM
sr_main_ylyj a,
mz_xzjg b
WHERE
a.sys_xzqh = b.xzqh_id
AND b.node_id LIKE '33%'
AND sys_scbj = 0
AND a.sys_spzt = 0
AND (
CASE
WHEN a.msfczdx IS NOT NULL
and a.msfczdx <>''
THEN
0
ELSE
1
END
) = '0'

----------------------------------------------

统计重复的数据

select count(1) as count,mhzsfz from sr_main GROUP BY mhzsfz HAVING count>1;

-------------------------------------------

过滤重复的数据ORDER BY

select mhzsfz from sr_main  GROUP BY mhzsfz ORDER BY mhzsfz limit 1000;

---------------------------------------------------

-- 查询结果作为查询结果的字段

SELECT
a.pk_sr_main AS pk_id,
a.mdjlx,
a.sys_xzqh,
(
SELECT
count(1)
FROM
sr_detail
WHERE
fk_sr_main = a.pk_sr_main
AND sys_scbj = 0
) AS mjtrk
FROM
sr_main a,
mz_xzjg b
WHERE
a.sys_xzqh = b.xzqh_id
AND a.pk_sr_main = ?

-----------------------------------------------------------------------------------

SELECT
? AS pk_hc_jz,
mhz AS jsqr,
'01' AS jzjlx,
mhzsfz AS jzjhm,
mjthkxz AS jhkxz,
mjtrk AS jjtzrk,
mzp AS jzp,
mzpurl AS jzpurl,
mlxdh AS jlxdh,
msjhm AS jsjhm,
mhkszd AS jhjdz,
msjjzd AS jjzdz,
mjtnzsr AS jkzpsrbgzj,
mjtzzc AS jhdrqzcbgzj,
mjtcczj AS jhdrqccbgzj,
sys_xzqh,
qhmc AS qhmc,
xzqh AS xzqh,
? AS sys_zdr,
? AS sys_zdrid,
? AS sys_zdrq,
0 AS sys_scbj,
0 AS sys_spzt,
? AS sys_sjbj,
'12' AS jhdsjd ,? AS jhdksrq ,? AS jhdjsrq ,? AS jzslbh ,? AS jhdxmbh ,? AS jhdxm,
pk_sr_main AS fk_wtyw_id,
0 AS jthbj,
mjtlb AS jz_xm_sam20,
? AS jhdrq,
? AS jhdpc,
2 AS sys_tjzt,
(
CASE
WHEN mgshd = 2 THEN -- 用于公示期发起核对：0：待发送，2：核对中，1：已核对
2
WHEN msyhd = 1 THEN -- 是否发起赡养人核对：1：是，0：否
1
ELSE
0
END
) AS jsyhd,
CASE (
CASE
WHEN mjtlb = 1 THEN -- mjtlb: 1=收入型低保;2=支出型贫困;3=残疾人单列户施保;5=重病型单列户施保
bz_str3 -- 收入型低保银行核查标准
WHEN mjtlb = 2 THEN
zcx_yhbz -- 支出型贫困银行核查标准
WHEN mjtlb = 3 THEN
cjr_yhbz -- 残疾人单列户施保银行核查标准
ELSE
zbx_yhbz -- 重病型单列户施保银行核查标准
END
)
WHEN 1 THEN
b.sbz * 48 * mxsrs
ELSE
b.sbz * 48 * mjtrk
END AS jhdbz,
b.sbz * mxsrs * b.xm_str16 AS jrsbz, -- xm_str16：家庭核查收入计算月份
mxsrs AS jz_bz_sam01,
(
SELECT
count(1)
FROM
sr_detail
WHERE
fk_sr_main = PK_SR_MAIN
AND dryxxlb = 'sy'
) * b.sbz * 12 * 10 AS jhdbz_sy,
b.xm_str16 AS jjtsrjsyf
FROM
sr_main a,
sr_dbbz b
WHERE
substr(a.sys_xzqh, 1, 6) = b.sjid
AND REPLACE (a.mjthkxz, 0, '') = b.syj7
AND a.mdjlx = b.sdjlx
AND b.sys_scbj = 0
AND b.bz_str1 = 1
AND pk_sr_main = ?

-----------------------------------------------------------------

SELECT
*
FROM
sr_main_ylyj a,
mz_xzjg b
WHERE
a.sys_xzqh = b.xzqh_id
AND b.node_id LIKE '33%'
AND sys_scbj = 0
AND sjbj = '1'
AND a.mclbj = '0'
AND (
CASE
WHEN a.msfczdx IS NOT NULL
AND a.msfczdx <> '' THEN
0
ELSE
1
END
) = '1'
ORDER BY
a.pk_sr_main_ylyj DESC
LIMIT 0,
15

--------------------------------------------------------------------------

入户调查绩效表统计

SELECT
sum(db_hs) AS db_hs,
sum(db_dchs) db_dchs,
sum(db_zb) AS db_zb
FROM
(
SELECT
substr(xzqh, 1, 6) AS xzqh,
(
SELECT
xjgmc
FROM
mz_xzjg c
WHERE
c.node_id = substr(xzqh, 1, 6)
) AS xjgmc,
SUM(db_hs) AS db_hs,
SUM(db_dchs) AS db_dchs,
IFNULL(
ROUND(db_dchs / db_hs, 2) * 100,
0
) AS db_zb
FROM
(
SELECT
substr(a.xzqh, 1, 6) AS xzqh,
(
SELECT
xjgmc
FROM
mz_xzjg c
WHERE
c.node_id = substr(a.xzqh, 1, 6)
) AS xjgmc,
SUM(db_hs) AS db_hs,
SUM(db_dchs) AS db_dchs
FROM
sr_rhdc a
WHERE
a.mjzksrq LIKE concat('2020-09', '%')
AND a.xzqh LIKE concat('3301', '%')
GROUP BY
substr(a.xzqh, 1, 6)
) e
GROUP BY
substr(e.xzqh, 1, 6)
) t

select ROUND(102/736, 2) * 100 as 比例;-- 14 四舍五入
select TRUNCATE(102/736, 2) * 100 as 比例;-- 13 不四舍五入

------------------------------------------------------------------------------------------

SELECT
CASE
WHEN node_level = 2 THEN
'全省共计'
WHEN node_level = 4 THEN
concat(qhmc_sj, '合计')
WHEN node_level = 6
AND qhmc = '钱塘新区' THEN
concat(qhmc_sj, qhmc)
ELSE
qhmc
END AS qhmc_show,
a.*
FROM
(
SELECT
(
SELECT
xjgmc
FROM
mz_xzjg
WHERE
node_id = (
CASE
WHEN length(t.xzqh) > 4 THEN
substr(t.xzqh, 1, 4)
ELSE
t.xzqh
END
)
) AS qhmc_sj,
t.*
FROM
sjycpc_report_tbwj t
WHERE
date = '2020-09'
) a
ORDER BY
xzqh

------------------------------------------------------------------------

-- 入户调查附件合计为0 附件表有的情况下
select pk_Sr_main,sum(case when fupcode='file_rhdcb' then 1 else 0 end) fjs,c.xqhmc,a.mhz,a.mhzsfz,a.mdjlx
from sr_main a,mz_file b, mz_xzjg c
where a.sys_spzt=1 and a.sys_djzt=1 and mdjlx in ('db_jz','wb_jz') and a.sys_scbj=0
and a.pk_Sr_main=b.fdbid and a.sys_xzqh=c.node_id
group by pk_Sr_main having sum(case when fupcode='file_rhdcb' then 1 else 0 end)=0
limit 50

-- 一个附件都没有的情况
select pk_sr_main,b.xqhmc,a.mhz,a.mhzsfz,a.mdjlx from sr_main a, mz_xzjg b where a.sys_spzt=1 and a.sys_djzt=1 and mdjlx in ('db_jz','wb_jz','kn_jz') and a.sys_scbj=0 and a.sys_xzqh=b.node_id
and pk_sr_main not in (
select pk_sr_main from sr_main a,mz_file b where a.sys_spzt=1 and a.sys_djzt=1 and mdjlx in ('db_jz','wb_jz','kn_jz') and a.sys_scbj=0 and a.pk_Sr_main=b.fdbid group by pk_sr_main)
limit 50

-----------------------------------------------------------------------------------
-- 入户调查表没有的数据，插入数据异常表
INSERT into sr_sjycpc (fk_id,mhz,mhzsfz,mdjlx,sys_clbj,sys_createtime,sys_xzqh,err_msg,qhmc,type,nf,yf)
select PK_SR_MAIN as fk_id,mhz,mhzsfz,mdjlx,0 as sys_clbj,sys_createtime,sys_xzqh,"缺少《入户调查表》" as err_msg,qhmc,2 as type,2020 as nf,10 as yf
from sr_main a,mz_file b, mz_xzjg c
where a.sys_spzt=1 and a.sys_djzt=1 and mdjlx in ('db_jz','wb_jz') and a.sys_scbj=0
and a.pk_Sr_main=b.fdbid and a.sys_xzqh=c.node_id
group by pk_Sr_main having sum(case when fupcode='file_rhdcb' then 1 else 0 end)=0
--------------------------------------------------------------------------------------------