MySQL多表多列数据高效聚合方法
在丰富结果集的过程中，常常需要整合来自多个表格的数据，每个表格包含感兴趣的特定列。然而，使用多个SELECT语句来实现这一点可能会很繁琐且效率低下。
假设我们需要从两个表中检索汇总列，如下例所示：
<code class="language-sql">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>这种方法通常会遇到问题，即只有第一列显示正确，而其他列的结果则错误。
解决方案是使用单个SELECT语句和条件聚合：
<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>在这个修改后的语句中，条件聚合使用IF()函数来评估每一行中特定的PAT_STATUS模式，对匹配的模式递增计数。然后，SUM()函数聚合这些计数，生成所需的结果集。
以上是如何高效地聚合 MySQL 中多个表的多个列的数据？的详细内容。更多信息请关注PHP中文网其他相关文章！
