求高手賜教一個查詢語句

[wenjinhe]

我現在有三個表，A表、B表、C表、
A表如下：
Team
A
B
C
D
E
......
B表如下：
BPro_no Team Date
0001 B 2005/01/02
0002 B 2005/02/03
0003 D 2005/02/05
0004 C 2005/03/01
0005 A 2005/03/06
.......

C表如下：
CPro_no Team Date
0001 A 2005/02/02
0002 C 2005/03/01
0003 B 2005/03/05
0004 C 2005/05/02
0005 E 2005/05/07
......

現在要求在指定某一時間段內（比如2005/01/01-2005/08/01）顯示如下效果：
Team B_total C_total
A 1 1
B 2 1
C 1 2
D 1 0
E 0 1
.......

求高手賜教這個查詢語句該怎么寫？

[wenjinhe]

好像TEAM没有起到筛选的效果~~请教高手帮忙看一下~~

[子陌红尘]

借用 samfeng_2003(凤翼天翔) 的测试数据：
-------------------------------------------------------------------------------
declare @a table(Team varchar(20))
insert @a
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E'

declare @b table(BPro_no varchar(20),Team varchar(20),Date datetime)
insert @b
select '1','B','2005/01/02' union all
select '2','B','2005/02/03' union all
select '3','D','2005/02/05' union all
select '4','C','2005/03/01' union all
select '5','A','2005/03/06'

declare @c table(CPro_no varchar(20),Team varchar(20),Date datetime)
insert @c
select '1','A','2005/02/02' union all
select '2','C','2005/03/01' union all
select '3','B','2005/03/05' union all
select '4','C','2005/05/02' union all
select '5','E','2005/05/07'


select
a.Team,
B_total=isnull((select count(*) from @b where Team=a.Team and Date between '2005-01-01' and '2005-08-01'),0),
C_total=isnull((select count(*) from @c where Team=a.Team and Date between '2005-01-01' and '2005-08-01'),0)
from
@a a

/*
Team B_total C_total
-------------------- ----------- -----------
A 1 1
B 2 1
C 1 2
D 1 0
E 0 1
*/

[wenjinhe]

請教高手~~

[samfeng_2003]

declare @a table(Team varchar(20))
insert @a
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E'

declare @b table(BPro_no varchar(20),Team varchar(20),Date datetime)
insert @b
select '1','B','2005/01/02' union all
select '2','B','2005/02/03' union all
select '3','D','2005/02/05' union all
select '4','C','2005/03/01' union all
select '5','A','2005/03/06'

declare @c table(CPro_no varchar(20),Team varchar(20),Date datetime)
insert @c
select '1','A','2005/02/02' union all
select '2','C','2005/03/01' union all
select '3','B','2005/03/05' union all
select '4','C','2005/05/02' union all
select '5','E','2005/05/07'

select *,
(select count(*) from @b b where date between '2005/01/01' and '2005/08/01' and a.Team=b.Team) as B_Total,
(select count(*) from @c c where date between '2005/01/01' and '2005/08/01' and a.Team=c.Team) as C_Total
from @a a

Team B_Total C_Total
-------------------- ----------- -----------
A 1 1
B 2 1
C 1 2
D 1 0
E 0 1

（所影响的行数为 5 行）

[子陌红尘]

select
a.Team,
B_total=isnull((select count(*) from B表 where Team=a.Team and Date between '2005-01-01' and '2005-08-01'),0),
C_total=isnull((select count(*) from C表 where Team=a.Team and Date between '2005-01-01' and '2005-08-01'),0)
from
A表 a

[wenjinhe]

樓上的libin_ftsafe朋友，怎么我查詢出來的數據不管是哪個Team的數據都是一樣的丫~~
效果如下：
Team B_Total C_Total
-------------------- ----------- -----------
A 5 5
B 5 5
C 5 5
D 5 5
E 5 5

好象是Team沒有起效果,請再次賜教~~

[wenjinhe]

非常感謝以上兩位，問題解決了~~