27,579
社区成员
发帖
与我相关
我的任务
分享
select b.deptname,d.Bm,d.StaffName,f.ProjectName,
sum(case when a.Sex=0 then PeopleNo else 0 end ) Mensex,
sum(case when a.Sex=0 then 0 else PeopleNo end )Womensex,
sum(PeopleNo) Sumrs,
sum(case when a.Sex=0 then c.Price*c.qrn else 0 end ) MenPrice,
sum(case when a.Sex=0 then 0 else c.Price*c.qrn end ) WoMenPrice,
sum(c.Price*c.qrn) SumPrice,
sum(case when a.PayType=2 then c.Price*c.qrn else 0 end )+sum(case when a.PayType=4 then c.Price*c.qrn else 0 end ) cash,
sum(c.Price*c.qrn)-sum(case when a.PayType=2 then c.Price*c.qrn else 0 end )-sum(case when a.PayType=4 then c.Price*c.qrn else 0 end ) Kh,
sum(case when a.designated=0 then 0 else PeopleNo end ) PNumbers,
sum(case when a.designated=0 then 0 else c.Price*c.qrn end ) PPrice,
sum(case when a.designated=0 then PeopleNo else 0 end ) fPNumbers,
sum(case when a.designated=0 then c.Price*c.qrn else 0 end ) fPPrice,
cast(sum(c.Price*c.qrn)/sum(PeopleNo) as numeric(18,2)) kdj
from OrderReservation a
left join basDept b on a.deptno=b.deptno
left join dbo.OrderReservationD c on a.OrderNo=c.OrderNo
left join dbo.basStaff d on c.staffno=d.staffno
left join dbo.basProject e on c.ProjectNo=e.ProjectNo
left join dbo.basProject f on e.ParentNo=f.ProjectNo
where a.deptno='010101'
group by b.deptname,d.Bm,d.StaffName,f.ProjectNo,f.ProjectName
order by f.ProjectNo
WITH T AS (
/* 你原先的SQL */
)
SELECT COALESCE(T1.deptname,T2.deptname,T3.deptname) AS 门店,
COALESCE(T1.Bm,T2.Bm,T3.Bm) AS 员工码,
COALESCE(T1.StaffName,T2.StaffName,T3.StaffName) AS 员工姓名,
T1.Mensex, T1.WomenSex,... -- 自己加字段别名
T2.Mensex, T2.WomenSex,... -- 自己加字段别名
T3.Mensex, T3.WomenSex,... -- 自己加字段别名
FROM (SELECT * FROM T WHERE ProjectName='剪发') T1
FULL JOIN (SELECT * FROM T WHERE ProjectName='烫发') T2
ON T1.Bm = T2.Bm
FULL JOIN (SELECT * FROM T WHERE ProjectName='染发') T3
ON T1.Bm = T3.Bm
OR T2.Bm = T3.Bm