比如现在有表 A
id kind money date
1 0 5 2013-01-01
2 0 10 2013-01-01
1 -1 -5 2013-06-01
3 0 5 2013-02-02最后怎么通过语句实现
id kind money date
1 -1 0 2013-06-01
2 0 10 2013-01-01
3 0 5 2013-02-02就是 如果有相同ID的把money加总,而date采用kind为-1那行的时间,
最后结果ID是都不重复的数据。
id kind money date
1 0 5 2013-01-01
2 0 10 2013-01-01
1 -1 -5 2013-06-01
3 0 5 2013-02-02最后怎么通过语句实现
id kind money date
1 -1 0 2013-06-01
2 0 10 2013-01-01
3 0 5 2013-02-02就是 如果有相同ID的把money加总,而date采用kind为-1那行的时间,
最后结果ID是都不重复的数据。
-> group by id
-> ;
+------+-----------+------------+------------+
| id | min(kind) | sum(money) | max(dt) |
+------+-----------+------------+------------+
| 1 | -1 | 0 | 2013-06-01 |
| 2 | 0 | 10 | 2013-01-01 |
| 3 | 0 | 5 | 2013-02-02 |
+------+-----------+------------+------------+
3 rows in set (0.05 sec)
t1.kind,
t1.money,
t2.date
from
(select id,
sum(kind) as kind,
sum(money) as money
from 表A
group by id) t1
inner join 表A t2
on t1.id=t2.id
and t1.kind=t2.kind
(select date from 表A where id=t.id order by kind desc limit 1) as date
from 表A t
group by id