多表联合查询中的棘手问题，非高手莫进！100分送上。

[tttk]

(1) bankcode(id, name) (银行机构表）
(2) data1 (id, bid, uploaded, ...) (数据1）
(3) data2 (id, bid, uploaded, ...) (数据2）
(4) data3 (id, bid, uploaded, ...) (数据3）

其中：data1, data2, data3 中的bid对应bankcode中的id字段。uploaded表示日期。

查询要求：根据bid字段统计出所有银行机构在指定时间内各数据表的统计。
结果结构如下：

id(银行编号), name(银行名称), data1_count, data2_count, data3_count
(其中dataX_count表示对应数据表dataX中某日期段的计数统计）

下面是我使用的查询语句，但是非常耗时，而且结果并不精确。
select u.bid, data1_count=count(d1.id), data2_count=count(d2.id), data3_count=count(d3.id)
from bankcode b
left join data1 d1 on d1.bid=b.id and d1.uploaded >='2005-9-1'
left join data2 d1 on d2.bid=b.id and d2.uploaded >='2005-9-1'
left join data3 d3 on d3.bid=b.id and d3.uploaded >='2005-9-1'
group by b.id

非常苦恼，请高手指点。

[wgsasd311]

select a.id,a.name,
data1_count=sum(case flag when '1' then 1 else 0 end),
data2_count=sum(case flag when '2' then 1 else 0 end),
data3_count=sum(case flag when '3' then 1 else 0 end)
from bankcode a left join
(
select bid,'1' as flag from d1 where uploaded >='2005-9-1'
union all

select bid,'2' as flag from d2 where uploaded >='2005-9-1'
union all
select bid,'3' as flag from d3 where uploaded >='2005-9-1'
) b on a.id=b.bid
group by a.id,a.name
order by a.id,a.name

[tttk]

感谢感谢！

[tttk]

上面的sql有点小错误。

select u.bid, data1_count=count(d1.id), data2_count=count(d2.id), data3_count=count(d3.id)
from bankcode b
left join data1 d1 on d1.bid=b.id and d1.uploaded >='2005-9-1'
left join data2 d2 on d2.bid=b.id and d2.uploaded >='2005-9-1'
left join data3 d3 on d3.bid=b.id and d3.uploaded >='2005-9-1'
group by b.id

[子陌红尘]

select
b.id,
data1_count = isnull(t1.data1_count,0),
data2_count = isnull(t2.data2_count,0),
data3_count = isnull(t3.data3_count,0)
from
bankcode b
left join
(select bid,count(id) as data1_count from d1 where uploaded >='2005-9-1' group bid) t1
on
b.id = t1.bid
left join
(select bid,count(id) as data2_count from d2 where uploaded >='2005-9-1' group bid) t2
on
b.id = t2.bid
left join
(select bid,count(id) as data3_count from d3 where uploaded >='2005-9-1' group bid) t3
on
b.id = t3.bid
order by
b.id

[Tempest1123]

up learning

[nimeide1234567890]

顶