跨多个列的SQL最小值

I'm trying to take a table with information as follows:

我想要一个包含如下信息的表格:

+----+---+---+
| ID | X | Y |
+----+---+---+
| A  | 1 | 3 |
| A  | 1 | 1 |
| A  | 1 | 2 |
| A  | 1 | 7 |
| B  | 2 | 2 |
| B  | 3 | 3 |
| B  | 1 | 9 |
| B  | 2 | 4 |
| B  | 2 | 1 |
| C  | 1 | 1 |
+----+---+---+

I'd like to be able to select the minimum across both columns, grouping by the first column - the "X" column is more important than the Y column. So for example, the query should return something like this:

我希望能够在两列中选择最小值，按第一列分组——“X”列比Y列更重要。例如，查询应该返回如下内容:

+----+---+---+
| ID | X | Y |
+----+---+---+
| A  | 1 | 1 |
| B  | 1 | 9 |
| C  | 1 | 1 |
+----+---+---+

Any ideas? I've gone through dozens of posts and experiments and no luck so far.

什么好主意吗?到目前为止，我已经经历了几十次的贴子和实验，但没有运气。

Thanks, James

谢谢你,詹姆斯

2 个解决方案

#1

You seem to want the row that has the minimum x value. And, if there are duplicates on x, then take the one with the minimum y.

你似乎想要的是x值最小的那一行。如果x上有重复项，那么取最小的y。

For this, use row_number():

为此,使用row_number():

select id, x, y
from (select t.*,
             row_number() over (partition by id order by x, y) as seqnum
      from t
     ) t
where seqnum = 1

If your database does not support window functions, you can still express this in SQL:

如果您的数据库不支持窗口函数，您仍然可以用SQL表示:

select t.id, t.x, min(t.y)
from t join
     (select id, MIN(x) as minx
      from t
      group by id
     ) tmin
     on t.id = tmin.id and t.x = tmin.minx
group by t.id, t.x

#2

If your RDBMS supports Window Function,

如果您的RDBMS支持窗口功能，

SELECT ID, X, Y
FROM
        (
            SELECT ID, X, Y,
                    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY X, Y) rn
            FROM    tableName
        ) d
WHERE   rn  = 1

SQLFiddle Demo
SQLFiddle演示

#1