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

MySQL多表多列数据高效聚合方法

在丰富结果集的过程中，常常需要整合来自多个表格的数据，每个表格包含感兴趣的特定列。然而，使用多个SELECT语句来实现这一点可能会很繁琐且效率低下。

假设我们需要从两个表中检索汇总列，如下例所示：

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

这种方法通常会遇到问题，即只有第一列显示正确，而其他列的结果则错误。

解决方案是使用单个SELECT语句和条件聚合：

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

在这个修改后的语句中，条件聚合使用IF()函数来评估每一行中特定的PAT_STATUS模式，对匹配的模式递增计数。然后，SUM()函数聚合这些计数，生成所需的结果集。

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