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