MySQL：如何找到客户在不同订单中重复订购的产品

Question

我尝试了很多，但当我找不到解决方案时，我将第一个问题发布在这里：

我有四张桌子：

    products
    ---------------
    id, name

    customers
    ------------------
    id, name

    orders
    -------------
    id, customer_id

    orders_products
    ---------------------------
    id, order_id, product_id

当然，一个客户只能在一个订单中订购一次产品

在一个查询中，我想返回每个customer\u id以及order\u id和product\u id，这些客户在多个订单中订购了任何产品。

换句话说，对于每个客户，我希望找到客户多次订购的不同产品（在单独的订单中），以及product\u id和order\u id。因此product\u id/order\u id应该重复，因为产品将在多个订单中订购多次。我不想返回只订购一次产品的记录。

我希望我足够清楚。

我已经有了样本数据和表格。编辑我的问题以发布用于创建数据库和表的sql：

CREATE DATABASE IF NOT EXISTS `test`
USE `test`;


CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7;

INSERT INTO `products` (`id`, `name`) VALUES
    (1, 'Car'),
    (2, 'Bus'),
    (3, 'Truck'),
    (4, 'Fan'),
    (5, 'Photo'),
    (6, 'Watch');

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6;

INSERT INTO `customers` (`id`, `name`) VALUES
    (1, 'Sukh'),
    (2, 'Sukh2'),
    (3, 'Ravi'),
    (4, 'Ravinder'),
    (5, 'Jas');


CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8;


INSERT INTO `orders` (`id`, `customer_id`) VALUES
    (1, 1),
    (2, 2),
    (3, 1),
    (4, 2),
    (5, 5),
    (6, 4),
    (7, 1);

CREATE TABLE IF NOT EXISTS `order_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL DEFAULT '0',
  `product_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7;

INSERT INTO `order_products` (`id`, `order_id`, `product_id`) VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 1, 2),
    (4, 4, 1),
    (5, 3, 5),
    (6, 7, 5);


select p.name, c.name, o.id as order_id, c.id as customer_id, p.id as product_id
from customers c inner join orders o on c.id=o.customer_id
inner join order_products op on op.order_id=o.id
inner join products p on p.id=op.product_id order by order_id

sukh2订购了订单号2和订单号4的汽车（一位客户多次订购的汽车），所以我想退回这两份记录，sukh订购了订单号3和订单号7的照片（一位客户多次订购的照片），所以这两份记录也应该总共退回4份。

我尝试了很多下面查询的变体，但没有得到正确的结果。我想我唯一错过的是在子查询结果上加入customer_id。所以我尝试了下面给定的查询，它似乎确实有效。@barmar建议有所帮助。

SELECT p.name, c.name, o.id AS order_id, c.id AS customer_id, p.id AS product_id
FROM customers c
INNER JOIN orders o ON c.id=o.customer_id
INNER JOIN order_products op ON op.order_id=o.id
INNER JOIN products p ON p.id=op.product_id
INNER JOIN (
SELECT customer_id, p.id AS product_id
FROM customers c
INNER JOIN orders o ON c.id=o.customer_id
INNER JOIN order_products op ON op.order_id=o.id
INNER JOIN products p ON p.id=op.product_id
GROUP BY c.id, p.id
HAVING COUNT(p.id) > 1) q2 ON q2.product_id=p.id AND q2.customer_id=c.id

能不能写的好一点，夜深了，我多试试，结果不对就发帖。

谢谢大家的帮助。

Answer 1

您可以将子查询中的联接表减少为订单和订单产品，因为它们已经保存了所有需要的信息（客户id和产品id）。您也可以使用计数（*）而不是计数（p.id）。

SELECT p.name, c.name, o.id AS order_id, c.id AS customer_id, p.id AS product_id
FROM customers c
INNER JOIN orders o ON c.id=o.customer_id
INNER JOIN order_products op ON op.order_id = o.id
INNER JOIN products p ON p.id = op.product_id
INNER JOIN (
    SELECT o.customer_id, op.product_id
    FROM  orders o 
    INNER JOIN order_products op ON op.order_id = o.id
    GROUP BY o.customer_id, op.product_id
    HAVING COUNT(*) > 1
) q2 ON q2.product_id = p.id AND q2.customer_id = c.id

这将返回与查询相同的结果。

name    product_id  name    customer_id     order_id
Car              1  Sukh2             2            2
Car              1  Sukh2             2            4
Photo            5  Sukh              1            3
Photo            5  Sukh              1            7

演示：https://www.db-fiddle.com/f/bq2WbLNGdKHGjowkR25VAr/0

但是，如果可以使用稍微不同的数据格式，则可以避免使用子查询，并使用聚合函数使用相同的表进行连接。

SELECT p.name, op.product_id, c.name, o.customer_id, GROUP_CONCAT(o.id) AS order_ids
FROM  orders o 
INNER JOIN order_products op ON op.order_id = o.id
INNER JOIN customers c ON c.id = o.customer_id
INNER JOIN products p on p.id = op.product_id
GROUP BY o.customer_id, op.product_id
HAVING COUNT(*) > 1

结果：

name    product_id  name    customer_id     order_ids
Photo            5  Sukh              1     7,3
Car              1  Sukh2             2     4,2

演示：https://www.db-fiddle.com/f/n1zvoPjpBVAW9yvjPH1fro/0

但是为了更好的性能，在子查询中进行聚合仍然是有意义的，从而减少需要为GROUP BY排序的行的大小。所以我可能会这样写查询：

SELECT p.name, q.product_id, c.name, q.customer_id, q.order_ids
FROM (
    SELECT o.customer_id, op.product_id, GROUP_CONCAT(o.id) order_ids
    FROM  orders o 
    INNER JOIN order_products op ON op.order_id = o.id
    GROUP BY o.customer_id, op.product_id
    HAVING COUNT(*) > 1
) q
INNER JOIN products p ON p.id = q.product_id
INNER JOIN customers c ON c.id = q.customer_id

演示：https://www.db-fiddle.com/f/v7xXW1cLgwAUR4DSGAomev/0