PHP+MySQL项目中如何将两个查询结果合并为一份报告

最近我在开发一个PHP+MySQL项目，围绕主表htsreg编写了两个SQL查询，分别用于统计不同维度的检测数据，以下是完整的查询代码和对应的结果：

Query 1：所有F类型检测的性别与年龄分组统计 #

这个查询用于统计**所有检测类型为F**的记录，按预设的年龄区间分组，计算每个区间内的男性、女性人数以及总人数。

SELECT COUNT(CASE WHEN SEX='M' THEN 1 END)AS MALE, COUNT(CASE WHEN SEX ='F' THEN 1 END)AS FEMALE, COUNT(*) AS TOTAL, `group` 
FROM `htsreg` 
INNER JOIN ( 
    SELECT 0 as `start`, 0.167 as `end`,'<=2months' as `group` UNION ALL 
    SELECT 0.168 as `start`, 1 as `end`, '3-12months' as `group` UNION ALL 
    SELECT 1.001 as `start`, 2 as `end`, '13-24months' as `group` UNION ALL 
    SELECT 2.001 as `start`, 4 as `end`, '25-59months' as `group` UNION ALL 
    SELECT 5 as `start`, 9 as `end`, '5-9years' as `group` UNION ALL 
    SELECT 10 as `start`, 14 as `end`, '10-14years' as `group` UNION ALL 
    SELECT 15 as `start`, 19 as `end`, '15-19years' as `group` UNION ALL 
    SELECT 20 as `start`, 24 as `end`, '20-24years' as `group` UNION ALL 
    SELECT 25 as `start`, 29 as `end`, '25-29years' as `group` UNION ALL 
    SELECT 30 as `start`, 34 as `end`, '30-34years' as `group` UNION ALL 
    SELECT 35 as `start`, 39 as `end`, '35-39years' as `group` UNION ALL 
    SELECT 40 as `start`, 44 as `end`, '40-44years' as `group` UNION ALL 
    SELECT 45 as `start`, 49 as `end`, '45-49years' as `group` UNION ALL 
    SELECT 50, 200, '50 years and above' 
) `sub` ON AGE BETWEEN `start` AND `end` 
WHERE (TESTTYPE ='F') 
GROUP BY `group`;

查询结果 #

Query 2：F类型检测且结果为阳性的性别与年龄分组统计 #

这个查询是在Query 1的基础上，增加了**最终结果为阳性（FINALRESULT='P'）**的筛选条件，统计符合要求的记录按年龄分组的性别及总人数。

SELECT COUNT(CASE WHEN SEX='M' THEN 1 END) AS MALE, COUNT(CASE WHEN SEX ='F' THEN 1 END) AS FEMALE, COUNT(*) AS TOTAL, `group` 
FROM `htsreg` 
INNER JOIN ( 
    SELECT 0 as `start`, 0.167 as `end`, '<=2months' as `group` UNION ALL 
    SELECT 0.168 as `start`, 1 as `end`, '3-12months' as `group` UNION ALL 
    SELECT 1.001 as `start`, 2 as `end`, '13-24months' as `group` UNION ALL 
    SELECT 2.001 as `start`, 4 as `end`, '25-59months' as `group` UNION ALL 
    SELECT 5 as `start`, 9 as `end`, '5-9years' as `group` UNION ALL 
    SELECT 10 as `start`, 14 as `end`, '10-14years' as `group` UNION ALL 
    SELECT 15 as `start`, 19 as `end`, '15-19years' as `group` UNION ALL 
    SELECT 20 as `start`, 24 as `end`, '20-24years' as `group` UNION ALL 
    SELECT 25 as `start`, 29 as `end`, '25-29years' as `group` UNION ALL 
    SELECT 30 as `start`, 34 as `end`, '30-34years' as `group` UNION ALL 
    SELECT 35 as `start`, 39 as `end`, '35-39years' as `group` UNION ALL 
    SELECT 40 as `start`, 44 as `end`, '40-44years' as `group` UNION ALL 
    SELECT 45 as `start`, 49 as `end`, '45-49years' as `group` UNION ALL 
    SELECT 50, 200, '50 years and above' 
) `sub` ON AGE BETWEEN `start` AND `end` 
WHERE (TESTTYPE ='F' AND FINALRESULT='P') 
GROUP BY `group`;

查询结果 #

内容的提问来源于stack exchange，提问作者A Alexio