年齡層統計SQL

標籤：年齡段統計

-- 處理名單丟失

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 

本文出自 “Java樂園” 部落格，請務必保留此出處http://hebinteng.blog.51cto.com/7967512/1971348

年齡層統計SQL