年龄层统计SQL-Java乐园

youngfan007

　　-- 处理名单丢失
　　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