Mysql distinct语法详解
0.背景
- 首先查看测试表的数据
mysql> select * from customers;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 163 | hangzhou |
| 9you | shanghai |
| alibaba | hangzhou |
| netease | hangzhou |
| 163 | beijing |
| alibaba | shanghai |
| 163 | beijing |
+-------------+----------+
7 rows in set (0.00 sec)
1.distinct 语法
distinct a,b
<=>distinct (a,b)
没问题
mysql> select
-> distinct customer_id
-> ,city
-> from customers;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 163 | hangzhou |
| 9you | shanghai |
| alibaba | hangzhou |
| netease | hangzhou |
| 163 | beijing |
| alibaba | shanghai |
+-------------+----------+
6 rows in set (0.00 sec)
- 但是distinct(customer_id,city)会报错
mysql> select
-> distinct (customer_id,city)
-> from customers;
ERROR 1241 (21000): Operand should contain 1 column(s)
select a, distinct b,
也会报错
mysql> select
-> city
-> ,distinct customer_id
-> from customers;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct customer_id
from customers' at line 3
distinct a
没问题
mysql> select
-> distinct customer_id
-> from customers;
+-------------+
| customer_id |
+-------------+
| 163 |
| 9you |
| alibaba |
| netease |
+-------------+
4 rows in set (0.00 sec)