继续请教简单问题？

[cakexiang]

刚刚的查询能够得到name和ver，但是id不能对应，我想要的是得到id
如下表：表名A，数据如下
id name ver
1 a001 v1.0
2 b002 v1.1
3 b002 v1.0
4 a001 v1.3
5 b002 v1.3
6 a001 v1.2
7 b002 v1.2

想得到每个名称下的最新版，如下：
name ver
4 a001 v1.3
5 b002 v1.3

SELECT id, name, max(substring(ver, 2)) as ver FROM `sup_web`.`test` group by name可以得到
name ver
1 a001 v1.3
2 b002 v1.3
id不能和最大版本对应，请问应该如何写？

[cakexiang]

谢谢

[vipper23]

SELECT max(id), name, max(substring(ver, 2)) as ver FROM `sup_web`.`test` group by name

[ACMAIN_CHM]

考虑到
v1.2
v11.0
v12.1
v9.0
v9.3
这种情况，你可以用这个语句。

select * from a t
where not exists (select 1 from a where name=t.name and mid(ver,2)+0>mid(t.ver,2)+0);

[ACMAIN_CHM]

mysql> select * from a;
+------+-------+----+
| name | ver   | id |
+------+-------+----+
| a001 | v1.0  |  1 |
| b002 | v1.1  |  2 |
| b002 | v1.0  |  3 |
| a001 | v1.3  |  4 |
| b002 | v1.3  |  5 |
| a001 | v1.2  |  6 |
| b002 | v1.2  |  7 |
| a001 | v11.0 |  8 |
| b002 | v12.1 |  9 |
| b002 | v9.0  | 10 |
| a001 | v9.3  | 11 |
+------+-------+----+
11 rows in set (0.00 sec)

mysql> select * from a t
    -> where not exists (select 1 from a where name=t.name and mid(ver,2)+0>mid(
t.ver,2)+0);
+------+-------+----+
| name | ver   | id |
+------+-------+----+
| a001 | v11.0 |  8 |
| b002 | v12.1 |  9 |
+------+-------+----+
2 rows in set (0.05 sec)

mysql>

[cakexiang]

[Quote=引用 1 楼 vipper23 的回复:]
SQL codeSELECTmax(id), name,max(substring(ver,2))as verFROM `sup_web`.`test`groupby name
[/Quote]
也只能得到
name ver
6 a001 v1.3
7 b002 v1.3
我想得到
name ver
4 a001 v1.3
5 b002 v1.3

[ACMAIN_CHM]

或者

mysql> select *
    -> from a t
    -> where mid(ver,2)+0=(select max(mid(ver,2)+0) from a where name=t.name);
+------+-------+----+
| name | ver   | id |
+------+-------+----+
| a001 | v11.0 |  8 |
| b002 | v12.1 |  9 |
+------+-------+----+
2 rows in set (0.00 sec)

mysql>

[ACMAIN_CHM]

另外一种写法，但效率并不好。

mysql> select t.*
    -> from a t inner join (select name, max(mid(ver,2)+0) as nv from a group by
 name) s
    ->  on t.name=s.name and mid(t.ver,2)+0=s.nv;
+------+-------+----+
| name | ver   | id |
+------+-------+----+
| a001 | v11.0 |  8 |
| b002 | v12.1 |  9 |
+------+-------+----+
2 rows in set (0.00 sec)

mysql>