如何在SQL中高效查询不同表的多列？

优化多表多列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

解决方案在于在单个查询中使用条件聚合。这种简化的方法根据指定条件计算多个列，提供准确的结果。 改进后的查询是：

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

此修改后的查询可有效检索必要的数据，消除与原始过于复杂的结构相关的错误。 关键是使用条件 SUM() 语句将计算合并为一个结构良好的查询。

以上是如何在SQL中高效查询不同表的多列？的详细内容。更多信息请关注PHP中文网其他相关文章！