version：5.7.27
-- SQL脚本
SELECT
te.total,
c.`name` AS districtName,
ifnull( ver_t.total, '0' ) AS verTotal,
c.CODE,
'' AS NAME,
'' AS address,
ifnull( ms_t.total, 0 ) AS msTotal,
ifnull( ms_tt.total, 0 ) AS hsTotal,
ifnull( wjf_t.total, 0 ) AS wjfTotal,
ifnull( tui_t.total, 0 ) AS tuiTotal,
(
CASE
WHEN jft_t.total = 0 THEN
'0.00%'
WHEN ( ver_t.total = 0 OR ver_t.total IS NULL ) THEN
'0.00%'
WHEN ROUND(( ver_t.total / jft_t.total )* 100, 2 )> 100 THEN
'100.00%' ELSE CONCAT( ROUND(( ver_t.total / jft_t.total )* 100, 2 ), '%' )
END
) AS bili,
(
CASE
WHEN ms_t.total = 0 THEN
'0.00%'
WHEN ( ms_tt.total = 0 OR ms_tt.total IS NULL ) THEN
'0.00%'
WHEN ROUND(( ms_tt.total / ms_t.total )* 100, 2 )> 100 THEN
'100.00%' ELSE CONCAT( ROUND(( ms_tt.total / ms_t.total )* 100, 2 ), '%' )
END
) AS msbili,
(
CASE
WHEN te.total = 0 THEN
'0.00%'
WHEN ( tui_t.total = 0 OR tui_t.total IS NULL ) THEN
'0.00%'
WHEN ROUND(( tui_t.total / te.total )* 100, 2 )> 100 THEN
'100.00%' ELSE CONCAT( ROUND(( tui_t.total / te.total )* 100, 2 ), '%' )
END
) AS zgbili
FROM
(
SELECT
s.district_no,
COUNT( s.id ) AS total
FROM
lv_seal s
WHERE
s.is_deleted = 'N'
AND s.city_no = '430700'
AND s.approval_date >= '20201001'
AND s.approval_date <= '20210930'
GROUP BY
s.district_no
ORDER BY
s.district_no DESC
) AS te
LEFT JOIN (
SELECT
s.district_no,
COUNT( s.id ) AS total
FROM
lv_seal s
WHERE
s.is_deleted = 'N'
AND STATUS = '4'
AND s.city_no = '430700'
AND s.fetch_date >= '20201001'
AND s.fetch_date <= '20210930'
GROUP BY
s.district_no
ORDER BY
s.district_no DESC
) AS jft_t ON te.district_no = jft_t.district_no
LEFT JOIN (
SELECT
s.district_no,
COUNT( s.id ) AS total
FROM
lv_seal s
WHERE
s.is_deleted = 'N'
AND vertify_type IN ( '1', '2' )
AND STATUS = '4'
AND s.city_no = '430700'
AND s.fetch_date >= '20201001'
AND s.fetch_date <= '20210930'
GROUP BY
s.district_no
ORDER BY
s.district_no DESC
) AS ver_t ON te.district_no = ver_t.district_no
LEFT JOIN lv_district c ON te.district_no = c.`code`
LEFT JOIN ( SELECT COUNT(*) AS total, district_no FROM lv_marker_site GROUP BY district_no ORDER BY district_no DESC ) AS ms_t ON c.CODE = ms_t.district_no
LEFT JOIN (
SELECT
COUNT(*) AS total,
district_no
FROM
(
SELECT
ms.`name`,
ms.marker_site_no,
s_t.total,
ms.city_no,
ms.district_no
FROM
lv_marker_site ms
LEFT JOIN (
SELECT
marker_site_no,
COUNT( id ) AS total
FROM
lv_seal
WHERE
marker_site_no IS NOT NULL
AND approval_date >= '20201001'
AND approval_date <= '20210930' GROUP BY marker_site_no ) AS s_t ON ms.marker_site_no = s_t.marker_site_no WHERE s_t.total IS NOT NULL AND s_t.total > 0
) AS h_ms_t
GROUP BY
h_ms_t.district_no
ORDER BY
h_ms_t.district_no DESC
) AS ms_tt ON ms_tt.district_no = ms_t.district_no
LEFT JOIN (
SELECT
s.district_no,
COUNT( s.id ) AS total
FROM
lv_seal s
WHERE
s.is_deleted = 'N'
AND vertify_type = '2'
AND s.city_no = '430700'
AND s.approval_date >= '20201001'
AND s.approval_date <= '20210930'
GROUP BY
s.district_no
ORDER BY
s.district_no DESC
) AS tui_t ON c.CODE = tui_t.district_no
LEFT JOIN (
SELECT
s.district_no,
COUNT( s.id ) AS total
FROM
lv_seal s
WHERE
s.is_deleted = 'N'
AND STATUS != '4'
AND s.city_no = '430700'
AND s.approval_date >= '20201001'
AND s.approval_date <= '20210930'
GROUP BY
s.district_no
ORDER BY
s.district_no DESC
) AS wjf_t ON c.CODE = wjf_t.district_no
WHERE
c.CODE IS NOT NULL
ORDER BY
c.CODE;
------------------------------------------------------------------------------------------------------------------------