Mysql 嵌套子查询

发布时间:2024年01月13日

文章目录

大家好!我是夏小花,今天是2024年1月13日|腊月初三

子查询

需求是:最外层的查询语句里面包含四个不相同表的查询,根据月份进行关联查询,每个查询语句中的where条件可以自行去定义,最后返回数量和月份
语法:

select a.月份,a.总数一,b.总数二,c.总数三,d.总数四
from (
         SELECT
             CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0')) AS Month,
             COUNT(DISTINCT m.id) AS 总数一
         FROM persion m
                  JOIN zs_yi br ON m.id = br.p.id
         WHERE
                 YEAR(br.measuring_time) = 2023 AND  (
                 (br.dbpval > m.dbpval_max OR br.dbpval < m.dbpval_min) OR
                 (br.sbpval > m.sbpval_max OR br.sbpval < m.sbpval_min)
        )
        GROUP BY CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0'))
        ) a left join
        (
        SELECT
        CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0')) AS Month,
        COUNT(DISTINCT m.id) AS xtCount
        FROM pserion m
        JOIN zs_er br ON m.id = br.p_id
        WHERE
        YEAR(br.measuring_time) = 2023 AND (
        (br.gluval > m.sugar_max OR br.gluval < m.sugar_min)
        )
        GROUP BY CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0'))
        ) b on a.月份 = b.月份 left join
        (
        SELECT
        CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0')) AS Month,
        COUNT(DISTINCT m.id) AS xzCount
        FROM pserion m
        JOIN zs_san br ON m.id = br.p_id
        WHERE
        YEAR(br.measuring_time) = 2023 AND (
        (br.total_cholesterol > m.blood_max OR br.total_cholesterol < m.blood_min)
        )
        GROUP BY CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0'))
        ) c on a.月份 = c.月份 left join
        (
        SELECT
        CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0')) AS Month,
        COUNT(DISTINCT m.id) AS tzCount
        FROM persion m
        JOIN zs_si br ON m.id = br.p_id
        WHERE
        YEAR(br.measuring_time) = 2023 AND (
        (br.bmi > m.bodyfat_max OR br.bmi < m.bodyfat_min)
        )
        GROUP BY CONCAT(YEAR(br.measuring_time), '-', LPAD(MONTH(br.measuring_time), 2, '0'))
        ) d on a.月份 = d.月份
        order by a.月份 asc
文章来源:https://blog.csdn.net/xiaohua616/article/details/135567723
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。