mysql之groupby进⾏分组统计
格式：
select 字段1，字段2
from 表名
where 条件
group by 字段
样例⼀：
1.需要每个市的对应数据
-- 计算审批完成时间和提交审批时间天数（总时间差）总数据量⾏政区划
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%'
group by substr(JXZJGBH,1,4)
2.各个地市的审批时效，审批时效：总的审批时间/总的审批数量
select d.xjgmc,round(zsj/zsl,2) 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 INSTR('db_jz,kn_jz,wb_jz,ls_jz',b.JSPXMBM)>0 -- 过滤单据类型审批状态和⽇期
groupby分组
group by substr(JXZJGBH,1,4)) c,mz_xzjg d
de_id
样例⼆：
对组进⾏过滤，having的使⽤
SELECT
distinct(pk_sr_main)
FROM
sr_main a,
sr_detail b
WHERE
a.pk_sr_main =
b.fk_sr_main
AND a.mdjlx = 'ls_jz'
AND b.dsfxs = '01'
AND b.dryxxlb = 'jt'
and a.sys_spzt = 1
and a.sys_djzt = 1
GROUP BY
fk_sr_main
HAVING
max(mxsrs) <> count(pk_sr_detail);
where与having的区别：where是对⾏进⾏过滤，having是对组进⾏过滤；

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。