现在有两张表.
表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 -(减) 第一项值 (减) 第二项值)
表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 -(减) 第一项值 (减) 第二项值)
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
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;
--------------
这个怎么定义呢?
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
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
+------+--------+
| 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>