有这样一张表,表示宠物信息
create table pet(id int ,ownerid int ,potential int ,level int );
insert into pet values (2,1,10,9),(1,1,10,10);
insert into pet values (3,2,10,11),(4,2,10,11);
insert into pet values (5,3,11,10);
insert into pet values (6,4,12,10),(7,4,12,9);
mysql> select *from pet;
+------+---------+-----------+-------+
| id | ownerid | potential | level |
+------+---------+-----------+-------+
| 2 | 1 | 10 | 9 |
| 1 | 1 | 10 | 10 |
| 3 | 2 | 10 | 11 |
| 4 | 2 | 10 | 11 |
| 5 | 3 | 11 | 10 |
| 6 | 4 | 12 | 10 |
| 7 | 4 | 12 | 9 |
+------+---------+-----------+-------+
7 rows in set (0.00 sec)现在要做一个排行榜 ,比如只取前4名,
条件是, 同一个主人只能有一名宠物参与排行, 排序按 potential ,level 降序,我试着写了一个
select id ,ownerid, potential ,level from
(select a.id,a.ownerid,a.potential,a.level from pet a,
(select ownerid,max(potential) as maxpotential from pet group by ownerid) b
where a.ownerid=b.ownerid and a.potential = b.maxpotential
) c group by ownerid order by potential desc ,level desc limit 0,4;
但是总感觉有问题。
+------+---------+-----------+-------+
| id | ownerid | potential | level |
+------+---------+-----------+-------+
| 6 | 4 | 12 | 10 |
| 5 | 3 | 11 | 10 |
| 3 | 2 | 10 | 11 |
| 2 | 1 | 10 | 9 |
+------+---------+-----------+-------+
4 rows in set (0.00 sec)
比如出来的结果第4条是不应该上榜的。id=2的level 比id=1的低
table
create table pet(id int ,ownerid int ,potential int ,level int );
insert into pet values (2,1,10,9),(1,1,10,10);
insert into pet values (3,2,10,11),(4,2,10,11);
insert into pet values (5,3,11,10);
insert into pet values (6,4,12,10),(7,4,12,9);
mysql> select *from pet;
+------+---------+-----------+-------+
| id | ownerid | potential | level |
+------+---------+-----------+-------+
| 2 | 1 | 10 | 9 |
| 1 | 1 | 10 | 10 |
| 3 | 2 | 10 | 11 |
| 4 | 2 | 10 | 11 |
| 5 | 3 | 11 | 10 |
| 6 | 4 | 12 | 10 |
| 7 | 4 | 12 | 9 |
+------+---------+-----------+-------+
7 rows in set (0.00 sec)现在要做一个排行榜 ,比如只取前4名,
条件是, 同一个主人只能有一名宠物参与排行, 排序按 potential ,level 降序,我试着写了一个
select id ,ownerid, potential ,level from
(select a.id,a.ownerid,a.potential,a.level from pet a,
(select ownerid,max(potential) as maxpotential from pet group by ownerid) b
where a.ownerid=b.ownerid and a.potential = b.maxpotential
) c group by ownerid order by potential desc ,level desc limit 0,4;
但是总感觉有问题。
+------+---------+-----------+-------+
| id | ownerid | potential | level |
+------+---------+-----------+-------+
| 6 | 4 | 12 | 10 |
| 5 | 3 | 11 | 10 |
| 3 | 2 | 10 | 11 |
| 2 | 1 | 10 | 9 |
+------+---------+-----------+-------+
4 rows in set (0.00 sec)
比如出来的结果第4条是不应该上榜的。id=2的level 比id=1的低
table
ORDER BY potential DESC ,LEVEL DESC
比如,我修改一下数据 ,
insert into pet values (1,1,10,9),(2,1,10,10);
insert into pet values (3,2,10,11),(4,2,10,11);
insert into pet values (5,3,11,10);
insert into pet values (6,4,12,10),(7,4,12,9);
出来的结果就不对了。
| 2 | 1 | 10 | 9 |
| 1 | 1 | 10 | 10 |取哪条记录
| 1 | 1 | 10 | 10 |
| 3 | 2 | 10 | 11 |
| 4 | 2 | 10 | 11 |SELECT * FROM pet a WHERE NOT EXISTS(SELECT 1 FROM pet WHERE a.`ownerid`=`ownerid`
AND (a.`potential`<`potential`
OR
a.`potential`=`potential` AND a.`level`<`level`)
OR
(a.`potential`=`potential` AND a.`level`=`level` AND a.`id`<id)
) ORDER BY potential DESC ,LEVEL DESC
否则:
SELECT * FROM pet a WHERE NOT EXISTS(SELECT 1 FROM pet WHERE a.`ownerid`=`ownerid`
AND (a.`potential`<`potential`
OR
a.`potential`=`potential` AND a.`level`<`level`)
OR
(a.`potential`=`potential` AND a.`level`=`level` AND a.`id`>id)
) ORDER BY potential DESC ,LEVEL DESC
;
;
from pet A
where not exists (select 1 from pet B where A.ownerid=B.ownerid and A.id<B.id)
order by potential desc ,level desc
limit 0,4;