想了大半天没想出来, 求一个聚合SQL怎么写

[xiaocai0001]

现在有两张表.
表A:
如下列:
name, number
data:
'a', 10
'b', 10

表B:
name, type,
data:
a, 'ta'
a, 'tb'
a, 'tc'
b, 'ta'

现在对表A中的每个name, 做如下计算
1.计数表B中对应每个name, type为'ta'的个数
2.计数表B中对应每个name, type为'tb'的个数
3.最终对每个name计算 (number -(减) 第一项值 (减) 第二项值)

[WWWWA]

select a.name,
sum(if(b.name='ta',1,0)) as ta,
sum(if(b.name='tb',1,0)) as tb,
max(a.number)-sum(if(b.name='ta',1,0))-sum(if(b.name='tb',1,0))
from ta a
left join tb b on a.name=b.name
group by a.name

[WWWWA]

or
将IF－＞CASE　WHEN　

[vinsonshen]

1，2：

select a.name, b.type, count(*) as 对应总个数 from 表A a inner join 表B on a.name=b.name where b.type in ('ta','tb') group by a.name, b.type;

[vinsonshen]

number -(减) 第一项值 (减) 第二项值
--------------
这个怎么定义呢？

[百年树人]

select 
  a.name,
  isnull(b.ta,0) as ta,
  isnull(c.tb,0) as tb,
  a.number-isnull(b.ta,0)-isnull(c.tb,0)
from
  a
left join
  (select name,count(1) as ta where type='ta' group by name) as b
on
  a.name=b.name
left join
  (select name,count(1) as tb where type='tb' group by name) as c
on
  a.name=c.name

[百年树人]

[Quote=引用 5 楼 josy 的回复:]
SQL codeselect
a.name,isnull(b.ta,0)as ta,isnull(c.tb,0)as tb,
a.number-isnull(b.ta,0)-isnull(c.tb,0)from
aleftjoin
(select name,count(1)as tawhere type='ta'groupby name)as bon
a.name=b.nam¡­
[/Quote]

select 
  a.name,
  isnull(b.ta,0) as ta,
  isnull(c.tb,0) as tb,
  a.number-isnull(b.ta,0)-isnull(c.tb,0)
from
  a
left join
  (select name,count(1) as ta from b where type='ta' group by name) as b
on
  a.name=b.name
left join
  (select name,count(1) as tb from b where type='tb' group by name) as c
on
  a.name=c.name

[ACMAIN_CHM]

mysql> select * from a;
+------+--------+
| name | number |
+------+--------+
| a    |     10 |
| b    |     10 |
+------+--------+
2 rows in set (0.00 sec)

mysql> select * from b;
+------+------+
| name | type |
+------+------+
| a    | ta   |
| a    | tb   |
| a    | tc   |
| b    | ta   |
+------+------+
4 rows in set (0.00 sec)

mysql>

现在对表A中的每个name, 做如下计算
1.计数表B中对应每个name, type为'ta'的个数

mysql> select a.name,
    ->  (select count(*) from b where name=a.name and type='ta') as tacnt
    -> from a;
+------+-------+
| name | tacnt |
+------+-------+
| a    |     1 |
| b    |     1 |
+------+-------+
2 rows in set (0.00 sec)

mysql>

mysql>

2.计数表B中对应每个name, type为'tb'的个数

mysql> select a.name,
    ->  (select count(*) from b where name=a.name and type='tb') as tacnt
    -> from a;
+------+-------+
| name | tacnt |
+------+-------+
| a    |     1 |
| b    |     0 |
+------+-------+
2 rows in set (0.00 sec)

mysql>

3.最终对每个name计算 (number -(减) 第一项值 (减) 第二项值)

mysql> select a.name,
    ->  (select count(*) from b where name=a.name and type='ta')
    ->  -(select count(*) from b where name=a.name and type='tb') as tacnt
    -> from a;
+------+-------+
| name | tacnt |
+------+-------+
| a    |     0 |
| b    |     1 |
+------+-------+
2 rows in set (0.00 sec)

mysql>

[xiaocai0001]

楼上高手, 解决了我的问题, 非常感谢!