复杂查询

[foxe]

表1:
id rid emp empdate emp1 emp1date emp1rej emp2 emp2date epm2rej
1 1 1001 2006-1-1 2002 2006-1-1 false 3003 2006-1-1 true
2 2 1002 2006-1-2 2002 2006-1-2 false 3003 2006-1-2 true
3 2 1002 2006-1-2 2002 2006-1-2 false 3003 2006-1-3 true

表2:
id rid sid rej_issue rej_type
1 1 1 NG DD
2 1 1 NG1 DM
3 2 2 NG DD
4 2 3 NG2 DE

表关系：表2的sid为表1的id
求:主报表：每个emp在empdate里emp,emp2rej = true 和emp3rej = true的数量
子报表：得到每个rej数量的详情，及1001 在 2006-1-1 那天 1 个 emprej2 的详情（查询表2可得)

[昵称被占用了]

重写下这两句

求:主报表：每个emp在empdate里emp,emp2rej = true 和emp3rej = true的数量
子报表：得到每个rej数量的详情，及1001 在 2006-1-1 那天 1 个 emprej2 的详情（查询表2可得)

[yyairr]

select count(emp) from 表1 where emp2rej = true and emp3rej = true order by empdate
select count(rej),emprej2 from 表2 where emp=1001 and empdate=2006-1-1

[LouisXIV]

...

What a poor chinese... --;

[foxe]

主报表：
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
----------------------------

[foxe]

报歉，描述不清楚，还是用表直观一点

[paoluo]

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
------------------------------------------------
emp_Qty的1，2，0怎麼計算的？？

[foxe]

就是根据 1001/1002 在每天出现的次数

[foxe]

一早上线,往前提一下,请大家帮忙啊

[foxe]

救火啊,大家快帮看看

[子陌红尘]

try：

select
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

[子陌红尘]

select
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

[foxe]

http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx?mfr=true
希望这个文章对我有所作用.