数据库查询过慢优化

可以尝试先建立嵌套子查询，将数据分成小规模化

原来

select
  s.school_id,
  s.school_name,
  sum(
    case
      when o.role = 'student' then 1
      else 0
    end
  ) as stu_num
from
  t_school as s 
  left join t_organization as o on s.school_id = o.school_id
 
group by
  s.school_id,
  s.school_name,


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
 

替换

select
  s.school_id,
  s.school_name,
  sum(
    case
      when o.role = 'student' then 1
      else 0
    end
  ) as stu_num
from
  t_school as s
  left join (
    select
      school_id sum(
        case
          when o.role = 'student' then 1
          else 0
        end
      ) as stu_num
    from
      t_organization
  ) as o on s.school_id = o.school_id
group by
  s.school_id,
  s.school_name,


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
 

文章来源: hiszm.blog.csdn.net，作者：孙中明，版权归原作者所有，如需转载，请联系作者。

原文链接：hiszm.blog.csdn.net/article/details/118971584