SQL-根据不同列中的值过滤重复行 [英] SQL - filter duplicate rows based on a value in a different column

问题描述

我有一个SQL表:

+-------------+-----------+---------+
|     ID      |  position |  user   |
+-------------+-----------+---------+
|      1      |     1     |     0   |
|      2      |     2     |     0   |
|      3      |     3     |     0   |
|      4      |     4     |     0   |
|      5      |     5     |     0   |
|      6      |     6     |     0   |
|      7      |     7     |     0   |
|      8      |     7     |     1   |
+-------------+-----------+---------+

我想根据位置列和用户列的不同值来过滤重复的行，对于第一个查询，我需要具有以下结果:

I would like to filter the duplicate row based on position column and the distinct value of user column, for the first query I need to have the following result:

+-------------+-----------+---------+
|     ID      |  position |  user   |
+-------------+-----------+---------+
|      1      |     1     |     0   |
|      2      |     2     |     0   |
|      3      |     3     |     0   |
|      4      |     4     |     0   |
|      5      |     5     |     0   |
|      6      |     6     |     0   |
|      8      |     7     |     1   |
+-------------+-----------+---------+

对于第二个查询，我需要以下内容:

For the second query I need the following:

+-------------+-----------+---------+
|     ID      |  position |  user   |
+-------------+-----------+---------+
|      1      |     1     |     0   |
|      2      |     2     |     0   |
|      3      |     3     |     0   |
|      4      |     4     |     0   |
|      5      |     5     |     0   |
|      6      |     6     |     0   |
|      7      |     7     |     0   |
+-------------+-----------+---------+

要实现此目的，我需要哪些查询?

What queries do I need to achieve this?

谢谢.

推荐答案

在没有更多信息的情况下，以下两个查询假设您要通过采用较大的(最大)user值来解析重复的位置，第一种情况，或者第二种情况下较小的(最小)user值.

In the absence of further information, the two queries below assume that you want to resolve duplicate positions by taking either the larger (maximum) user value, in the first case, or the smaller (minimum) user value in the second case.

第一个查询:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT position, MAX(user) AS max_user
    FROM yourTable
    GROUP BY position
) t2
    ON t1.position = t2.position AND
       t1.user     = t2.max_user

第二个查询:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT position, MIN(user) AS min_user
    FROM yourTable
    GROUP BY position
) t2
    ON t1.position = t2.position AND
       t1.user     = t2.min_user

这篇关于SQL-根据不同列中的值过滤重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！