SELECT
    t1.規則編號 AS 編碼,
    t1.規則描述 AS 名稱,
    SUM( CASE WHEN t3.DATA_SOURCES = '00' THEN 1 ELSE 0 END ) AS '類型01',
    SUM( CASE WHEN t3.DATA_SOURCES = '01' THEN 1 ELSE 0 END ) AS '類型02',
    SUM( CASE WHEN t3.DATA_SOURCES = '02' THEN 1 ELSE 0 END ) AS '類型03',
    SUM( CASE WHEN t3.DATA_SOURCES = '03' THEN 1 ELSE 0 END ) AS '類型04' 
FROM
    (
SELECT
    'A_M_0001' AS 規則編號,
    '規則01' AS 規則描述 UNION ALL
SELECT
    'A_M_0002' AS 規則編號,
    '規則02' AS 規則描述 UNION ALL
SELECT
    'A_M_0003' AS 規則編號,
    '規則03' AS 規則描述 UNION ALL
SELECT
    'A_M_0005' AS 規則編號,
    '規則04' AS 規則描述 UNION ALL
SELECT
    'A_M_0007' AS 規則編號,
    '規則05' AS 規則描述 UNION ALL
SELECT
    'A_M_0006' AS 規則編號,
    '規則06' AS 規則描述 UNION ALL
SELECT
    'A_M_0008' AS 規則編號,
    '規則07' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_01' AS 規則編號,
    '規則08' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_12' AS 規則編號,
    '規則09' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_02' AS 規則編號,
    '規則10' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_03' AS 規則編號,
    '規則11' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_13' AS 規則編號,
    '規則12' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_05' AS 規則編號,
    '規則13' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_11' AS 規則編號,
    '規則14' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_06' AS 規則編號,
    '規則15' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_14' AS 規則編號,
    '規則16' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_07' AS 規則編號,
    '規則17' AS 規則描述 UNION ALL
SELECT
    'A_J_0001_15' AS 規則編號,
    '規則18' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_01' AS 規則編號,
    '規則19' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_02' AS 規則編號,
    '規則20' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_03' AS 規則編號,
    '規則21' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_04' AS 規則編號,
    '規則22' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_05' AS 規則編號,
    '規則23' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_06' AS 規則編號,
    '規則24' AS 規則描述 UNION ALL
SELECT
    'A_J_0002_07' AS 規則編號,
    '規則25' AS 規則描述 UNION ALL
SELECT
    'A_J_0003_01' AS 規則編號,
    '規則26' AS 規則描述 UNION ALL
SELECT
    'A_J_0003_02' AS 規則編號,
    '規則27' AS 規則描述 UNION ALL
SELECT
    'A_J_0003_05' AS 規則編號,
    '規則28' AS 規則描述 
    ) t1
    LEFT JOIN RAMS_TRIAL_CHECKLIST t2 ON t2.RULE_CODE like concat('%',t1.規則編號,'%')
    LEFT JOIN RAMS_TRIAL_CHECKLIST_EXT t3 ON t2.CHECKLIST_ID = t3.CHECKLIST_ID 
WHERE
    DATE( t2.UPDATE_TIME ) = CURDATE( ) - INTERVAL 1 DAY 
GROUP BY t1.規則編號,t1.規則描述;

SELECT '60019311' AS code, '北京' AS name
union all
SELECT '60019312' AS code, '上海' AS name
union all
SELECT '60019313' AS code, '廣州' AS name
union all
SELECT '60019314' AS code, '重慶' AS name

RAMS_TRIAL_CHECKLIST t2 ON t2.RULE_CODE like concat('%',t1.規則編號,'%')

CASE WHEN t3.DATA_SOURCES = '00' THEN 1 ELSE 0 END

SELECT COUNT(ID) FROM DATA WHERE DATE( UPDATE_TIME ) = CURDATE( ) - INTERVAL 1 DAY