这是对优秀答案的后续问题:
SQL Select only rows with Max Value on a Column
SQLFiddlehttp://sqlfiddle.com/#!2/3077f/2
表“yourtable”:
| id | val | ignore | content |
-------------------------------
| 1 | 10 | 0 | A |
| 1 | 20 | 0 | B |
| 1 | 30 | 1 | C |
| 2 | 40 | 0 | D |
| 2 | 50 | 0 | E |
| 2 | 60 | 1 | F |
查找每个id的最大值时,使用以下sql:
select yt1.*
from yourtable yt1
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.val < yt2.val )
where yt2.id is null;
所以在这种情况下
| id | val | ignore | content |
-------------------------------
| 1 | 30 | 1 | C |
| 2 | 60 | 1 | F |
问题是当它等于1时,如何按“忽略”列过滤,结果将是
| id | val | ignore | content |
-------------------------------
| 1 | 20 | 0 | B |
| 2 | 50 | 0 | E |
最佳答案
您需要将条件同时放入子查询和外部查询中:
select yt1.*
from yourtable yt1 left outer join
yourtable yt2
on yt1.id = yt2.id and yt1.val < yt2.val and yt2.ignore <> 1
where yt2.id is null and yt1.ignore <> 1;
关于mysql - SQL仅选择按列过滤的列中具有最大值的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25245008/