Hello,大家好!我是程序员阿飞!最近几周一直在做统计相关的功能,因此,我分享几个感觉比较好的SQL语句给大家,希望对大家能够起到帮助以及学习的作用。
一、完整SQL
SELECT
e.org_id,
e.org_name,
CONVERT ( f.wgyCount, SIGNED ) wgyCount ,
CONVERT ( f.dayDurCount, SIGNED ) dayDurCount,
IFNULL( CAST( (( f.dayDurCount / f.wgyCount ) * 100 ) AS DECIMAL ( 8, 2 ) ), 0 ) dayDurPassRate,
CONVERT ( f.dayLenCount, SIGNED ) dayLenCount,
IFNULL( CAST( (( f.dayLenCount / f.wgyCount ) * 100 ) AS DECIMAL ( 8, 2 ) ), 0 ) dayLenPassRate
FROM
(
SELECT RIGHT
( sub_id, 36 ) id,
IFNULL( SUM( t.wgyCount ), '0' ) wgyCount ,
IFNULL( SUM( t.dayDurCount ), '0' ) dayDurCount ,
IFNULL( SUM( t.dayLenCount ), '0' ) dayLenCount
FROM
(
SELECT
wc.wgyCount,
ddc.dayDurCount,
dlc.dayLenCount,
ORG_NAME,
LEFT ( eoo.ORG_SEQ, ( SELECT 36 + CHAR_LENGTH( eo.ORG_SEQ ) FROM enterprise_org_object eo WHERE eo.ORG_ID = 'ec9525dc-d8a6-4608-b6b0-92be07721d78' ) ) sub_id
FROM
enterprise_org_object eoo
LEFT JOIN (
SELECT
eurc.org_id,
count( eurc.USER_Id ) wgyCount
FROM
enterprise_user_role_child eurc
WHERE
eurc.role_id = 8
GROUP BY
eurc.org_id
) wc ON wc.org_id = eoo.org_id
LEFT JOIN (
SELECT
eurc.ORG_ID orgId,
count( srd.childId ) dayDurCount
FROM
enterprise_user_role_child eurc
JOIN (
SELECT
csr.childId,
csr.locTime,
IFNULL( td.duration, '00:00:00' ) duration,
( CASE WHEN td.duration IS NULL THEN 0 ELSE ( TIME_TO_SEC( td.duration ) ) / 60 END ) changeTime
FROM
(
SELECT
sr.user_role_child_id childId,
sr.check_id checkId,
DATE_FORMAT( sr.location_time, '%Y-%m-%d' ) locTime
FROM
sign_record sr
RIGHT JOIN ( SELECT user_role_child_id, MAX( location_time ) AS "maxTime" FROM sign_record GROUP BY user_role_child_id ) tmp ON sr.location_time = tmp.maxTime
AND sr.user_role_child_id = tmp.user_role_child_id
WHERE
sr.user_role_child_id IS NOT NULL
) csr
LEFT JOIN (
SELECT
a.location_time locTime,
a.check_id checkId,
a.duration
FROM
trajectory_data AS a
LEFT JOIN ( SELECT MAX( location_time ) AS 'maxTime', check_id FROM trajectory_data GROUP BY check_id ) AS b ON a.check_id = b.check_id
WHERE
a.location_time = b.maxTime
) td ON td.checkId = csr.checkId
) srd ON eurc.ID = srd.childId
WHERE
eurc.ROLE_ID = 8
AND srd.changeTime > (SELECT ac.duration FROM assess_config ac WHERE ac.assess_type = '2' AND ac.del_flag = '0' )
AND srd.locTime = '2019-11-23'
GROUP BY
eurc.ID
) ddc ON ddc.orgId = eoo.org_id
LEFT JOIN (
SELECT
eurc.ORG_ID orgId,
count( srd.childId ) dayLenCount
FROM
enterprise_user_role_child eurc
JOIN (
SELECT
csr.childId,
csr.locTime,
IFNULL(td.length,'0.00') length
FROM
(
SELECT
sr.user_role_child_id childId,
sr.check_id checkId,
DATE_FORMAT( sr.location_time, '%Y-%m-%d' ) locTime
FROM
sign_record sr
RIGHT JOIN ( SELECT user_role_child_id, MAX( location_time ) AS "maxTime" FROM sign_record GROUP BY user_role_child_id ) tmp ON sr.location_time = tmp.maxTime
AND sr.user_role_child_id = tmp.user_role_child_id
WHERE
sr.user_role_child_id IS NOT NULL
) csr
LEFT JOIN (
SELECT
a.location_time locTime,
a.check_id checkId,
a.length
FROM
trajectory_data AS a
LEFT JOIN ( SELECT MAX( location_time ) AS 'maxTime', check_id FROM trajectory_data GROUP BY check_id ) AS b ON a.check_id = b.check_id
WHERE
a.location_time = b.maxTime
) td ON td.checkId = csr.checkId
) srd ON eurc.ID = srd.childId
WHERE
eurc.ROLE_ID = 8
AND srd.length > (SELECT ac.distance FROM assess_config ac WHERE ac.assess_type = '1' AND ac.del_flag = '0' )
AND srd.locTime = '2019-11-23'
GROUP BY
eurc.ID
) dlc ON dlc.orgId = eoo.org_id
) t
GROUP BY
sub_id
) f
LEFT JOIN enterprise_org_object e ON f.id = e.org_id
WHERE
e.ORG_SEQ LIKE '%ec9525dc-d8a6-4608-b6b0-92be07721d78%'
AND e.PARENT_ORG_ID = 'ec9525dc-d8a6-4608-b6b0-92be07721d78'
ORDER BY
e.ORG_ORDER
二、SQL分析
1、分组获取最新的一条数据
SELECT
sr.user_role_child_id childId,
sr.check_id checkId,
DATE_FORMAT( sr.location_time, '%Y-%m-%d' ) locTime
FROM
sign_record sr
RIGHT JOIN ( SELECT user_role_child_id, MAX( location_time ) AS "maxTime" FROM sign_record GROUP BY user_role_child_id ) tmp ON sr.location_time = tmp.maxTime
AND sr.user_role_child_id = tmp.user_role_child_id
WHERE
sr.user_role_child_id IS NOT NULL
2、关联子表获取分组后的最新一条数据
SELECT
csr.childId,
csr.locTime,
IFNULL( td.duration, '00:00:00' ) duration,
( CASE WHEN td.duration IS NULL THEN 0 ELSE ( TIME_TO_SEC( td.duration ) ) / 60 END ) changeTime
FROM
(
SELECT
sr.user_role_child_id childId,
sr.check_id checkId,
DATE_FORMAT( sr.location_time, '%Y-%m-%d' ) locTime
FROM
sign_record sr
RIGHT JOIN ( SELECT user_role_child_id, MAX( location_time ) AS "maxTime" FROM sign_record GROUP BY user_role_child_id ) tmp ON sr.location_time = tmp.maxTime
AND sr.user_role_child_id = tmp.user_role_child_id
WHERE
sr.user_role_child_id IS NOT NULL
) csr
LEFT JOIN (
SELECT
a.location_time locTime,
a.check_id checkId,
a.duration
FROM
trajectory_data AS a
LEFT JOIN ( SELECT MAX( location_time ) AS 'maxTime', check_id FROM trajectory_data GROUP BY check_id ) AS b ON a.check_id = b.check_id
WHERE
a.location_time = b.maxTime
) td ON td.checkId = csr.checkId
三、SQL结果
cbdfec9f-1f8b-4fe0-8e21-eecf88317a0b 珠岙镇 86 0 0.00 0 0.00
7e199cbd-7c39-4e19-aa34-fa4cb2730eaa 海游街道 76 0 0.00 0 0.00
8ce1d2a4-7f5d-49d9-b821-3353c8021f8f 海润街道 34 0 0.00 0 0.00
dcef8d91-cff5-4590-9330-a070892414ad 横渡镇 26 0 0.00 0 0.00
3702bb74-a5c9-400d-a2dd-cebc9c452502 花桥镇 35 0 0.00 0 0.00
fb8aabee-118e-405a-be8e-2a2cc251b18b 健跳镇 76 0 0.00 0 0.00
1846d787-e9c8-4629-aaaa-fd5ace721955 蛇蟠乡 6 0 0.00 0 0.00
f90b73c7-37fd-48d0-9100-c380d1d85ae0 沙柳街道 35 0 0.00 0 0.00
b656b49f-a155-4545-b0a7-30b41550bb14 浦坝港镇 94 0 0.00 0 0.00
ea7a5dca-c933-49e3-b3ea-1274a290872e 亭旁镇 112 1 0.89 1 0.89
6814b9eb-883e-4cd8-b981-fb98b8fc9e06 三门经济开发区管委会 2 0 0.00 0 0.00
76947639-0fa6-48e9-9e68-007187e2a807 三门县沿海工业城管理委员会 2 0 0.00 0 0.00
e6897d17-dc8c-4ced-b5cb-4b28f0c67a3a 绛帐镇 3 0 0.00 0 0.00
四、SQL总结
1、CONVERT ( f.wgyCount, SIGNED ) 获取该字段的整数部分,即将该字段值转换成整数
2、IFNULL( SUM( t.dayLenCount ), '0' ) 如果该字段值不为空,则取值,否则默认为“0”
3、CAST( (( f.dayLenCount / f.wgyCount ) * 100 ) AS DECIMAL ( 8, 2 ) ) 求取两个数的百分比值并保留两位小数
4、TIME_TO_SEC( td.duration ) ) / 60 将时间类型的字段值转换成分钟
5、CASE WHEN td.duration IS NULL THEN 0 ELSE ( TIME_TO_SEC( td.duration ) ) / 60 END 如果某一字段值为空时取0,否则取另一个值
6、DATE_FORMAT( sr.location_time, '%Y-%m-%d' ) locTime 将时间类型的字段值进行格式化转化
7、CHAR_LENGTH( eo.ORG_SEQ ) 获取某一字段的长度
8、LEFT ( eoo.ORG_SEQ , 108 ) sub_id 获取某一字段中从左边起前108位的字符串
9、RIGHT ( sub_id, 36 ) 获取某一字段从右边起36位的字符串