表和数据:
CREATE TABLE test1 (pid INT,id INT,LEVEL INT);insert into `test1`(`pid`,`id`,`level`)
values
(1228582896,1306,10),
(1228582896,1307,21),
(1228582896,1308,40),
(1228582897,1303,10),
(1228582897,1304,21),
(1228582897,1305,40),
(1228582898,1299,10),
(1228582898,1300,20),
(1228582898,1301,40),
(1228582901,1315,10),
(1228582901,1316,21),
(1228582901,1317,40),
(1228582902,1312,10),
(1228582902,1313,20),
(1228582902,1314,40),
(1228582903,1309,10),
(1228582903,1310,20),
(1228582903,1311,40);我想得到以下的结果,每个PID最大的level,如下:
pid id level
1228582896 1308 40
1228582897 1305 40
1228582898 1301 40
1228582901 1317 40
1228582902 1314 40
1228582903 1311 40请指教,谢谢。
我是这样写得,但是ID不对。
SELECT pid,id, MAX(LEVEL) FROM test1 GROUP BY pid
求教。
CREATE TABLE test1 (pid INT,id INT,LEVEL INT);insert into `test1`(`pid`,`id`,`level`)
values
(1228582896,1306,10),
(1228582896,1307,21),
(1228582896,1308,40),
(1228582897,1303,10),
(1228582897,1304,21),
(1228582897,1305,40),
(1228582898,1299,10),
(1228582898,1300,20),
(1228582898,1301,40),
(1228582901,1315,10),
(1228582901,1316,21),
(1228582901,1317,40),
(1228582902,1312,10),
(1228582902,1313,20),
(1228582902,1314,40),
(1228582903,1309,10),
(1228582903,1310,20),
(1228582903,1311,40);我想得到以下的结果,每个PID最大的level,如下:
pid id level
1228582896 1308 40
1228582897 1305 40
1228582898 1301 40
1228582901 1317 40
1228582902 1314 40
1228582903 1311 40请指教,谢谢。
我是这样写得,但是ID不对。
SELECT pid,id, MAX(LEVEL) FROM test1 GROUP BY pid
求教。
having count(b.pid)=0;
+------------+---------+------------+
| pid | max(id) | MAX(LEVEL) |
+------------+---------+------------+
| 1228582896 | 1308 | 40 |
| 1228582897 | 1305 | 40 |
| 1228582898 | 1301 | 40 |
| 1228582901 | 1317 | 40 |
| 1228582902 | 1314 | 40 |
| 1228582903 | 1311 | 40 |
+------------+---------+------------+
6 rows in set (0.00 sec)
from test1 a, (select pid, max(a.level) as max_level from test1 a where a.pid = pid group by a.pid) b
where a.pid = b.pid and a.level = b.max_level
分组取最大N条记录方法征集
SELECT * FROM test1 ORDER BY pid,LEVEL DESC ) AS t
GROUP BY pid先排序,再分组。