1、union即将多个表的数据合并到一个表中,union all保留重复行 示例,获取用户性别统计:
select count(*) as total,sex from ((select userid,sex from `user_base_info_1` )
UNION ALL (select userid,sex from `user_base_info_2` )
UNION ALL (select userid,sex from `user_base_info_3` )
UNION ALL (select userid,sex from `user_base_info_4` )
UNION ALL (select userid,sex from `user_base_info_5` )
UNION ALL (select userid,sex from `user_base_info_6` )
UNION ALL (select userid,sex from `user_base_info_7` )
UNION ALL (select userid,sex from `user_base_info_8` )
UNION ALL (select userid,sex from `user_base_info_9` )
UNION ALL (select userid,sex from `user_base_info_10` )
UNION ALL (select userid,sex from `user_base_info_11` )
UNION ALL (select userid,sex from `user_base_info_12` )
UNION ALL (select userid,sex from `user_base_info_13` )
UNION ALL (select userid,sex from `user_base_info_14` )
UNION ALL (select userid,sex from `user_base_info_15` )
UNION ALL (select userid,sex from `user_base_info_16` )
UNION ALL (select userid,sex from `user_base_info_17` )
UNION ALL (select userid,sex from `user_base_info_18` )
UNION ALL (select userid,sex from `user_base_info_19` )
UNION ALL (select userid,sex from `user_base_info_20` )) as a group by sex
2、case where else对个别字段进行判断处理 示例获取不同年龄段的用户统计:
SELECT SUM(`人数`) AS total,`年龄段` FROM (
(SELECT nnd AS '年龄段',COUNT(*) AS '人数' FROM(
SELECT
CASE
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<25 THEN '25以下'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=25 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=34 THEN '25-34'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=35 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=44 THEN '35-44'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=45 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=54 THEN '45-54'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=55 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=64 THEN '55-64'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=65 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=69 THEN '65-69'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=70 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=74 THEN '70-74'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=75 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=79 THEN '75-79'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=80 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=84 THEN '80-84'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=85 THEN '85以上'
ELSE
'未知'
END
AS nnd FROM user_base_info_1
)a GROUP BY nnd) UNION ALL
(SELECT nnd AS '年龄段',COUNT(*) AS '人数' FROM(
SELECT
CASE
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<25 THEN '25以下'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=25 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=34 THEN '25-34'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=35 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=44 THEN '35-44'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=45 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=54 THEN '45-54'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=55 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=64 THEN '55-64'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=65 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=69 THEN '65-69'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=70 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=74 THEN '70-74'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=75 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=79 THEN '75-79'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=80 AND (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )<=84 THEN '80-84'
WHEN (YEAR(NOW())-YEAR(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') )>=85 THEN '85以上'
ELSE
'未知'
END
AS nnd FROM user_base_info_2
)a GROUP BY nnd)
) AS b GROUP BY `年龄段`;