如何有效聚合同一个 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>

高效方法（带条件聚合的单查询）：

最佳解决方案涉及使用条件聚合的单个查询：

<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() 聚合每个 DAY_IN 的这些计数。该方法显着提高了效率并提供了准确的结果。 它避免了通过连接多个子查询隐式创建的笛卡尔积。

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