Mysql使用distinct和inner join进行分组

下面是一个示例代码，演示如何在MySQL中使用DISTINCT和INNER JOIN进行分组：

-- 创建示例表
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Customer 1'),
(2, 'Customer 2'),
(3, 'Customer 3');

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 1, '2020-01-01'),
(2, 1, '2020-01-02'),
(3, 2, '2020-01-03'),
(4, 2, '2020-01-04'),
(5, 3, '2020-01-05'),
(6, 3, '2020-01-06');

-- 使用DISTINCT和INNER JOIN进行分组查询
SELECT DISTINCT customers.customer_name, COUNT(orders.order_id) AS order_count
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_name;

运行上述代码后，将得到如下结果：

+---------------+-------------+
| customer_name | order_count |
+---------------+-------------+
| Customer 1    |           2 |
| Customer 2    |           2 |
| Customer 3    |           2 |
+---------------+-------------+

结果显示了客户的名称和他们的订单数量。这里使用了DISTINCT关键字来确保返回的结果集中的每个客户名称仅出现一次。INNER JOIN用于将两个表连接在一起，通过共享的customer_id列进行连接。最后，使用GROUP BY子句将结果按客户名称分组，并使用COUNT函数计算每个客户的订单数量。