MySQL-如何选择对应列值的第一个重复行 [英] MySQL-How to select first duplicate row of corresponding column value

问题描述

我有一个包含三列(A，B，C)的表.所有列的数据类型均为varchar().在这里，我只选择那些初始行，它们是A列的第一个重复值.像A列具有不同的值(1,2,3)，因此初始行在列中的值为1需要检索.还有一件事，我的数据集中没有时间戳列.

I have a table that contains three columns(A,B,C). All columns are of datatype varchar().Here i want to select only those initial rows which is first duplicate value of column A. Like column A has distinct value (1,2,3) so the initial row that has value 1 in column A need to be retrieved.one more thing i don't have a timestamp column in my dataset.

For value 1 in column A, i need to retrieve row that has 1,2,1 value for column (A,B,C)
For value 2 in column A, i need to retrieve row that has 2,4,1 value for column (A,B,C)
For value 3 in column A, i need to retrieve row that has 3,1,1 value for column (A,B,C)

A|B|C
-----  
1|2|1
3|1|2
3|3|1
2|4|1
3|1|2
3|1|4
1|2|3

Now i want a Output Table as describe below.

A|B|C
-----
1|2|1
3|1|2
2|4|1

推荐答案

请考虑以下内容.为简单起见，我使用的是auto_incrementing id，但是唯一的时间戳也可以正常工作...

Consider the following. For simplicity, I'm using an auto_incrementing id but a unique timestamp would work just as well...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table 
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,A INT NOT NULL
 ,B INT NOT NULL
 ,C INT NOT NULL 
 ,UNIQUE(a,b,c)
 );

 INSERT INTO my_table (a,b,c) VALUES
 (1 ,2 ,1),
 (1 ,1 ,2),
 (2 ,3 ,1),
 (2 ,4 ,1),
 (3 ,1 ,2),
 (3 ,1 ,4),
 (3 ,2 ,3);

 SELECT x.* FROM my_table x ORDER BY id;
 +----+---+---+---+
 | id | A | B | C |
 +----+---+---+---+
 |  1 | 1 | 2 | 1 |
 |  2 | 1 | 1 | 2 |
 |  3 | 2 | 3 | 1 |
 |  4 | 2 | 4 | 1 |
 |  5 | 3 | 1 | 2 |
 |  6 | 3 | 1 | 4 |
 |  7 | 3 | 2 | 3 |
 +----+---+---+---+

 SELECT x.* 
   FROM my_table x 
   JOIN 
      ( SELECT a
             , MIN(id) min_id 
          FROM my_table 
         GROUP 
            BY a
      ) y 
     ON y.a = x.a 
    AND y.min_id = x.id 
  ORDER 
     BY id;
 +----+---+---+---+
 | id | A | B | C |
 +----+---+---+---+
 |  1 | 1 | 2 | 1 |
 |  3 | 2 | 3 | 1 |
 |  5 | 3 | 1 | 2 |
 +----+---+---+---+

有关此部分的更多信息，请参见手册( http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html ).

For more information on this soution, see the manual (http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html).

这篇关于MySQL-如何选择对应列值的第一个重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！