某月工作中班组人数等于3的班组SQL

      在班组信息中，没有存储班组所属的部门，这样我们在查某部门中某个班组在某月中的信息时，需要通过其他中间表进行关联，下面是一个查询质量信息时关联方式的语句。

 

--1.
select a.*, count(*) 人数
  from (select distinct cth.header_id   班组id,
                        cth.team_number 班组号,
                        cth.team_name   班组名称
          from qa_results qr, cux_team_headers cth, bom_departments bd
        
         where 1 = 1
           and qr.organization_id = 103
           and qr.plan_id = 100
           and qr.character28 = cth.team_number
           and qr.character17 >= '2010/09/01'
           and qr.character17 <= '2010/09/30'
           and bd.department_id = qr.department_id
           and bd.department_code like 'LY%'
           and bd.organization_id = 103) a,
       cux_team_lines ctl
 where 1 = 1
   and a.班组id = ctl.header_id
 group by a.班组id, a.班组号, a.班组名称
having count(*) = 3

union all
--2.
select a.*, count(*) 人数
  from (select distinct cth.header_id   班组id,
                        cth.team_number 班组号,
                        cth.team_name   班组名称
        
          from qa_results qr, cux_team_headers cth, bom_departments bd
        
         where 1 = 1
           and qr.organization_id = 103
           and qr.plan_id = 103
           and qr.character23 = cth.team_number
           and qr.character20 >= '2010/09/01'
           and qr.character20 <= '2010/09/30'
           and bd.department_id = qr.department_id
           and bd.department_code like 'LY%'
           and bd.organization_id = 103) a,
       cux_team_lines ctl
 where 1 = 1
   and a.班组id = ctl.header_id
 group by a.班组id, a.班组号, a.班组名称
having count(*) = 3