又一个还复杂的查询问题

[dx]

表的结构如下:
id numb name
1 11 aa
2 12 aa
3 13 bb
4 14 cc

有几个同样结构的表

要求如下:
统计所有表中name=aa的记录个数,并显示numb

[wangw98]

select numb,(select count(name) from tablename b where b.name=a.name) from tablename a

[LoveSQL]

select sum(case name when 'aa' then 1 else 0 end ) as 个数,sum(numb) as numb
from
(
select * from table1
union all select * from table2
union all select * from table3
--....你的所有表都列出来就行了
)a