要求查出每级网格的上报事件的数量,其中,网格是xx.xx.xx.xx的层级关系
首先 出每级网格
SELECT
s.orgname,
s.ORGID
FROM
sys_org_info s
WHERE
s.ORGID LIKE '37.02.82%'
AND s.ORGLEVEL = 4
然后与主表 事件表外链接 取数,其中 on的 链接条件是 ON e1.ORGID LIKE CONCAT(s1.ORGID, '%')
全部sql
SELECT
s1.orgname,
s1.ORGID,
sum(CASE WHEN e1.SOURCE_TYPE_ID = '3702820401' THEN 1 ELSE 0 END) wg,
sum(CASE WHEN e1.SOURCE_TYPE_ID = '3702820402' THEN 1 ELSE 0 END) wx,
count(e1.GUID) su
FROM
(
SELECT
*
FROM
event_base_info e
WHERE
1 = 1
) e1
RIGHT JOIN (
SELECT
s.orgname,
s.ORGID
FROM
sys_org_info s
WHERE
s.ORGID LIKE '37.02.82%'
AND s.ORGLEVEL = 4
) AS s1 ON e1.ORGID LIKE CONCAT(s1.ORGID, '%')
WHERE
1 = 1
GROUP BY
s1.ORGID
ORDER BY
s1.ORGID
查询 结果