求联合查询的方法

[tjhaua11]

目前有表A有三列uid，pid，name，一个uid可以有多个pid。根据pid分表10个B1到B10，每个表里面pid是可以重复的插入数据
现在的需求是列出任意uid对应pid在B表中的数量,谁帮忙给个sql语句?
举个例子:
A:
uid pid name
1 1001 a
2 1002 b
1 2002 c


B1:
pid xxx
1001 x
2001 x
1001 x
1001 x

B2:
pid xxx
1002 x
2002 x
1002 x


假如要查uid为1的对应pid数量的结果应该是：
uid pid name count()
1 1001 a 3
1 2002 c 1

[snbxp]

select a.uid,a.pid,a.name,count(1) cnt from A,
(
select pid from B1 union all
select pid from B2 union all
...
select pid from B10
)B
where a.pid=b.pid
group by a.uid,a.pid,a.name

[tjhaua11]

3ku，前辈。

引用 1 楼 snbxp 的回复:

select a.uid,a.pid,a.name,count(1) cnt from A,
(
select pid from B1 union all
select pid from B2 union all
...
select pid from B10
)B
where a.pid=b.pid
group by a.uid,a.pid,a.name