select Dress.PersonnelCode ,Dress.SpecID,sum(SpecSize.尺寸字段) from Dress,SpecSize
where Dress.SizeID= SpecSize.SizeID
group by PersonnelCode ,SpecID
where Dress.SizeID= SpecSize.SizeID
group by PersonnelCode ,SpecID
要求的是:列出各个人每一种种类的尺寸!
如下格式:
deptcode personnelcode specname sizename
1 1 df 1*2
1 1 d null
2 2 df null
2 2 d null
select P.DeptCode,P.PersonnelCode,PD.SpecName,PD.SizeName
from (select distinct DeptCode,PersonnelCode from Dress) as P,
(select D.SpecName,S.SizeName
from DressSpec D inner join DressSpecSize DS inner join DressSize S
on DS.SizeID = S.SizeID on D.SpecID = DS.SpecID) PD--某种类无尺寸也显示
select P.DeptCode,P.PersonnelCode,PD.SpecName,PD.SizeName
from (select distinct DeptCode,PersonnelCode from Dress) as P,
(select D.SpecName,S.SizeName
from DressSpec D Left join DressSpecSize DS inner join DressSize S
on DS.SizeID = S.SizeID on D.SpecID = DS.SpecID) PD