如何高效地聚合 MySQL 中不同表的多列数据？

高效汇总 MySQL 中多列数据

使用关系数据库通常需要聚合分布在不同表中多个列的数据。 简单地连接多个查询可能会导致结果不准确。 此示例演示了一个常见问题及其使用 MySQL 的有效解决方案。

考虑这个初始 MySQL 查询，旨在汇总 t_hospital 表中的患者数据：

<code class="language-sql">SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;

SELECT* from
   ( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )e,
     (SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE PAT_STATUS  like '%ong%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN ) a,
        (SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE PAT_STATUS  like '%rtde%'and  DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )b,
           (SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE PAT_STATUS  like '%pol%'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN   )c,
              (SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE PAT_STATUS  like '%para%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )d;</code>

问题：只有arr（算上DAY_IN）会产生正确的结果。 由于单独查询的连接不正确，ONG1、RTED、POLI 和 para（计算不同 PAT_STATUS 值的出现次数）不准确。

解决方案：在单个查询中使用 MySQL 的聚合函数（SUM 和 IF）提供了更高效、更准确的解决方案：

<code class="language-sql">SELECT DAY_IN, COUNT(*) AS arr,
        SUM(IF(PAT_STATUS  like '%ong%', 1, 0)) AS ONG1,
        SUM(IF(PAT_STATUS  like '%rtde%', 1, 0)) AS RTED,
        SUM(IF(PAT_STATUS  like '%pol%', 1, 0)) AS POL1,
        SUM(IF(PAT_STATUS  like '%para%', 1, 0)) AS para
FROM t_hospital 
WHERE DAY_IN between @start_check and @finish_check 
    and RES_DATE between @start_res and @finish_res  
    and ID_daily_hos =@daily_hos
GROUP BY DAY_IN;</code>

此修订后的查询正确聚合每个​​ DAY_IN 的数据，在单个高效查询中为每个 PAT_STATUS 类别提供准确的计数。 这种方法避免了笛卡尔积并导致原始查询不准确。

以上是如何高效地聚合 MySQL 中不同表的多列数据？的详细内容。更多信息请关注PHP中文网其他相关文章！