重写下这两句求:主报表:每个emp在empdate里emp,emp2rej = true 和emp3rej = true的数量
子报表:得到每个rej数量的详情,及1001 在 2006-1-1 那天 1 个 emprej2 的详情(查询表2可得)
子报表:得到每个rej数量的详情,及1001 在 2006-1-1 那天 1 个 emprej2 的详情(查询表2可得)
调试欢乐多
select count(rej),emprej2 from 表2 where emp=1001 and empdate=2006-1-1
emp date emp_Qty emp1rej_Qty emp2rej_Qty
1001 2006-1-1 1 0 1
1002 2006-1-2 2 0 1
1002 2006-1-3 0 0 1子表:(当点击emp2rej_qty时展开)
点第1个
-----------------------------
rej_issue rej_type
NG DD
NG1 DM
-------------------------------点第2个
-----------------------------
rej_issue rej_type
NG DD
-----------------------------点第3个
-----------------------------
rej_issue rej_type
NG2 DE
----------------------------
1001 2006-1-1 1 0 1
1002 2006-1-2 2 0 1
1002 2006-1-3 0 0 1
------------------------------------------------
emp_Qty的1,2,0怎麼計算的??
b.emp,
a.empdate,
sum(case when a.empdate=b.empdate then 1 else 0 end),
sum(case when a.empdate=b.emp1date and b.emp1rej=true then 1 else 0 end),
sum(case when a.empdate=b.emp2date and b.emp2rej=true then 1 else 0 end)
from
(select distinct empdate from 表1
union
select distinct emp1date from 表1
union
select distinct emp2date from 表1) a
cross join
表1 b
group by
b.emp,a.empdate
emp = b.emp,
date = a.empdate,
emp_Qty = sum(case when a.empdate=b.empdate then 1 else 0 end),
emp1rej_Qty = sum(case when a.empdate=b.emp1date and b.emp1rej=true then 1 else 0 end),
emp2rej_Qty = sum(case when a.empdate=b.emp2date and b.emp2rej=true then 1 else 0 end)
from
(select distinct empdate from 表1 union select distinct emp1date from 表1 union select distinct emp2date from 表1) a
cross join
表1 b
group by
b.emp,a.empdate
希望这个文章对我有所作用.