name | finished |
+------------+----------+
| chenlu | 38 |
| chenshuang | 30 |
| chenzh | 28 |
| hanj | 1 |
| houz | 89 |
| liangxj | 15 |
| liaod | 72 |
| liujuny | 2 |
| maz | 14 |
| pij | 55 |
| wanghx2 | 3 |
| wenr | 627 |
| xiesy | 66 |
| zhaic | 65 |
| zhouzh | 233 |
| zhuxuan | 58 |
| zhuyan | 9 |
| | 30 |
| chenlu | 45 |
| chenshuang | 12 |
| chenzh | 10 |
| houz | 19 |
| liangx2 | 7 |
| liangxj | 3 |
| liaod | 6 |
| liujuny | 13 |
| maz | 10 |
| pij | 3 |
| wanghx2 | 2 |
| wenr | 247 |
| zhaic | 8 |
| zhouzh | 1 |
| zhuxuan | 12 |
| zhuyan | 7 |
+------------+----------+
数据如上:现想将其转换成
name finished unfinished
chenlu 38 45chenshuang 30 12
请问大虾们该如何实现?
姓名 分数 姓名 分数1 分数2
a 90 ------------> a 90 60
a 60
select a.name,b1.finished as finished,c1.finished as unfinished from tta
inner join
(select name,min(id) as minid,max(id) as maxid from tt group by name) b1 on a.id=b1.minid and a.name=b1.name
inner join
(select name,min(id) as minid,max(id) as maxid from tt group by name) c1 on a.id=c1.maxid and a.name=c1.name
a 85
a 85
这种现象,仅靠SQL语句则无法实现。加入ID后
1 a 85
9 a 85 则可以参考一下面贴子中介绍的几种方法。
http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx
如果没有主键,且不能添加这个字段,则只能靠程序或存储过程来解决了。