-- 處理名單丟失

SELECT?

? u.`user_id` u_userid,

? o.`user_id` o_userid,

? o.*?

FROM

? `tb_gh_orders` o?

? LEFT JOIN `tb_gh_user` u?

? ? ON o.`user_id` = u.`user_id`?

WHERE o.`course_id` = 128?

? AND o.`order_state` = '1' ;

-- 年齡段統計

SELECT?

? T1.*,

? CONCAT(LEFT (T1.C1 / T2.C2 * 100, 5), '%') P?

FROM

? (SELECT CASE WHEN SUBSTRING(U.ID_NUM, 7, 3)='' THEN '無×××' ELSE?

? ? CONCAT(SUBSTRING(U.ID_NUM, 9, 1), '0后') END AGE,

? ? SUBSTRING(U.ID_NUM, 7, 3) YEAR1,

? ? COUNT(*) C1?

? FROM

? ? TB_GH_USER U?

? GROUP BY SUBSTRING(U.ID_NUM, 7, 3)) T1,(SELECT COUNT(*) C2 FROM TB_GH_USER U2) T2?

---------------CRM年齡層統計

SELECT T1.*,CONCAT(LEFT (T1.C1 / T2.C2 * 100, 5), '%') P FROM

(SELECT CASE?

WHEN tt.age BETWEEN 1 AND 10 THEN '1-10'

WHEN tt.age BETWEEN 11 AND 20 THEN '11-20'

WHEN tt.age BETWEEN 21 AND 30 THEN '21-30'

WHEN tt.age BETWEEN 31 AND 40 THEN '31-40'

WHEN tt.age BETWEEN 41 AND 50 THEN '41-50'

WHEN tt.age BETWEEN 51 AND 60 THEN '51-60'

WHEN tt.age BETWEEN 61 AND 70 THEN '61-70'

WHEN tt.age BETWEEN 71 AND 80 THEN '71-80'

WHEN tt.age BETWEEN 81 AND 90 THEN '81-90'

END year_range,

COUNT(*) C1

?FROM (SELECT TIMESTAMPDIFF(YEAR, u.`birthDate`, CURDATE()) age,u.birthDate,o.`org_name`

?FROM `tb_crm_businessuser` u,`tb_crm_organ` o WHERE u.`organid`=o.`org_id` ?) tt?

?WHERE tt.age IS NOT NULL?

?GROUP BY year_range ) T1

?,(SELECT?

? ? COUNT(*) C2?

? FROM

? ? tb_crm_businessuser U2 WHERE u2.birthDate IS NOT NULL) T2?

---------------CRM按機構統計

SELECT tt.org_name,

COUNT(*) orgtotal,

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p11-20',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p21-30',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 31 AND 40 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p31-40',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 41 AND 50 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p41-50',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 51 AND 60 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p51-60',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 61 AND 70 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p61-70',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 71 AND 80 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p71-80',

SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END ) AS '11-20',

SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END ) AS '21-30',

SUM(CASE WHEN age BETWEEN 31 AND 40 THEN 1 ELSE 0 END ) AS '31-40',

SUM(CASE WHEN age BETWEEN 41 AND 50 THEN 1 ELSE 0 END ) AS '41-50',

SUM(CASE WHEN age BETWEEN 51 AND 60 THEN 1 ELSE 0 END ) AS '51-60',

SUM(CASE WHEN age BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) AS '61-70',

SUM(CASE WHEN age BETWEEN 71 AND 80 THEN 1 ELSE 0 END ) AS '71-80'

FROM (SELECT TIMESTAMPDIFF(YEAR, u.birthDate, CURDATE()) age,u.birthDate,o.org_name

?FROM tb_crm_businessuser u,tb_crm_organ o WHERE u.organid=o.org_id AND u.`birthDate` IS NOT NULL ) tt?

?GROUP BY tt.org_name