刚刚的查询能够得到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.3SELECT 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不能和最大版本对应,请问应该如何写?
如下表:表名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.3SELECT 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不能和最大版本对应,请问应该如何写?
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);
+------+-------+----+
| 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>
name ver
6 a001 v1.3
7 b002 v1.3
我想得到
name ver
4 a001 v1.3
5 b002 v1.3
-> 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>
-> 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>