SQL仅选择按列过滤的列中具有最大值的行 [英] SQL Select only rows with Max Value on a Column FILTERED by Column

问题描述

这是对最佳答案的后续问题：

SQL仅选择列上具有最大值的行

This is a followup question to the excellent answer:
SQL Select only rows with Max Value on a Column

SQLFiddle http://sqlfiddle.com/#!2/3077f/2

SQLFiddle http://sqlfiddle.com/#!2/3077f/2

表您的表：

| 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的最大val时，使用以下sql：

When looking for max val per id, following sql is used:

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;

因此在这种情况下结果将是

So the result will be in this case

| id | val | ignore | content |
-------------------------------
| 1  | 30  |   1    |   C     |
| 2  | 60  |   1    |   F     |

问题是如何在忽略列中进行过滤= 1，因此结果将是

The Question is how to filter out by column "ignore" when it's =1 so the result will be

| id | val | ignore | content |
-------------------------------
| 1  | 20  |   0    |   B     |
| 2  | 50  |   0    |   E     |

推荐答案

您需要将条件同时放在子查询和外部查询：

You need to put the condition both in the subquery and the outer query:

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;

这篇关于SQL仅选择按列过滤的列中具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！