选择显示第一个唯一值的行-Selecttherowsthatappearwiththefirstuniquevalue

0  

I see three ways to do this, depending on whether you have a column that uniquely defines the order of the records, and which non-standard feature of MySql you are willing to use.

我看到有三种方法可以做到这一点，具体取决于你是否有一个唯一定义记录顺序的列，以及你愿意使用的MySql的非标准功能。

Let's say you have such a column and it is called id, then do this:

假设您有这样一个列，它被称为id，然后执行以下操作：

SELECT C1, C2, C3
FROM   T1
WHERE  id IN (
         SELECT Min(id)
         FROM   T1
         GROUP  BY C1);

In this case you rely completely on the order that MySql chooses for your query result.

在这种情况下，您完全依赖于MySql为查询结果选择的顺序。

There are two ways I can suggest to produce the desired result in this case, and since the first one is rather easy, the second one might not be that interesting after all.

在这种情况下，有两种方法可以建议产生所需的结果，并且由于第一种方法相当容易，所以第二种方法可能并不那么有趣。

2.1 Non-standard GROUP BY clause

MySql allows you to do a GROUP BY in combination with a SELECT list that contains non-grouped-by columns, which are not aggregated either. This is not allowed in standard SQL, and other engines refuse that kind of queries.

MySql允许您将GROUP BY与包含非分组列的SELECT列表结合使用，这些列也未聚合。标准SQL中不允许这样做，其他引擎拒绝这种查询。

But it works in MySql:

但它适用于MySql：

SELECT C1, C2, C3
FROM   T1
GROUP BY C1;

2.2. MySql User Variables

In this case you could get the desired output with user variables. During the execution of the SELECT you can collect the C1 values you have visited in a variable, and then add as a WHERE condition that any next C1 value may not be present in this variable:

在这种情况下，您可以使用用户变量获得所需的输出。在执行SELECT期间，您可以收集您在变量中访问过的C1值，然后添加为WHERE条件，以便此变量中可能不存在任何下一个C1值：

SELECT      C1, C2, C3
FROM        T1,
            (SELECT @done := '') as init
WHERE       NOT find_in_set(C1, @done)
AND         (@done := concat_ws(',', @done, C1)) is not null;

Output in all three cases:

C1      C2        C3
---------------------
A       1         8
B       2         7
C       4         3

SQL fiddle of last solution.

最后解决方案的SQL小提琴。

Explanation

The order of evaluation is as follows:

评估顺序如下：

• The FROM clause is evaluated, and there the engine will assign the empty string to the variable @done;
• 评估FROM子句，引擎将空字符串赋给变量@done;
• For every record in the table, the WHERE clause is evaluated. If the first condition succeeds, it means that the value of C1 was never visited before, and so the next condition is evaluated, which always yield true, but will as a side-effect add the value of C1 to the string @done, separated by a comma;
• 对于表中的每个记录，将评估WHERE子句。如果第一个条件成功，则意味着之前从未访问过C1的值，因此评估下一个条件，它总是产生true，但是作为副作用将C1的值添加到字符串@done中，分开用逗号;
• If the first condition of the WHERE fails, this means the value of C1 was already visited before, and so the record will not be output. Also, because the first condition fails, the engine will not evaluate the second clause, as it already determined that the whole WHERE fails.
• 如果WHERE的第一个条件失败，这意味着之前已经访问过C1的值，因此不会输出记录。此外，由于第一个条件失败，引擎将不会评估第二个子句，因为它已经确定整个WHERE失败。