MYSQL从两列中选择DISTINCT值？
要在两列中选择不同的值，可以在MySQL中使用least()和greatest()函数。
让我们创建一个包含两列的表-
mysql> create table SelectDistinctTwoColumns −> ( −> StudentId int, −> EmployeeId int −> );
现在您可以在表中插入记录。插入记录的查询如下-
mysql> insert into SelectDistinctTwoColumns values(100,101); mysql> insert into SelectDistinctTwoColumns values(102,103); mysql> insert into SelectDistinctTwoColumns values(104,105); mysql> insert into SelectDistinctTwoColumns values(100,101); mysql> insert into SelectDistinctTwoColumns values(102,103); mysql> insert into SelectDistinctTwoColumns values(106,107); mysql> insert into SelectDistinctTwoColumns values(104,105); mysql> insert into SelectDistinctTwoColumns values(105,104);
在select语句的帮助下显示表中的所有记录。查询如下-
mysql> select *from SelectDistinctTwoColumns;
以下是输出-
+-----------+------------+ | StudentId | EmployeeId | +-----------+------------+ | 100 | 101 | | 102 | 103 | | 104 | 105 | | 100 | 101 | | 102 | 103 | | 106 | 107 | | 104 | 105 | | 105 | 104 | +-----------+------------+ 8 rows in set (0.00 sec)
看上面的输出。在两列中都可以看到一些重复的值。这是从列中选择不同值的查询-
mysql> select distinct least(StudentId, EmployeeId) as FirstColumn, −> greatest(StudentId, EmployeeId) as SecondColumn from SelectDistinctTwoColumns;
以下是输出-
+-------------+--------------+ | FirstColumn | SecondColumn | +-------------+--------------+ | 100 | 101 | | 102 | 103 | | 104 | 105 | | 106 | 107 | +-------------+--------------+ 4 rows in set (0.00 sec)