MySQL 根据 JOIN 中的 2 个或更多键删除重复项

Question

这是基本场景。

我正在尝试计算用户在特定 session 期间添加到购物车的商品数量。单个用户可以拥有多个购物车和多个 session 。但是，我看到的是，在同一购物车中的同一 session 期间，相同的product_id被添加了两次(错误)。

肯定会出现这样的情况:同一用户在不同 session 中将同一产品添加到不同的购物车。由于 session_id 和 cart_id 具有单射 (1-1) 关系，因此我们不能将相同的 product_id 添加两次。

如何在单个 session 的单个购物车中删除重复的product_id？ 请注意，当 action_name = ADD 时，将对扫描进行计数。所以存在依赖

请注意，该表是由 JOIN 命令生成的，因此应在代码期间进行重复删除。

这是代码和表格

SELECT   u.userId
    ,c.session_id
    ,c.cartId
    ,i.product_Id
    ,i.action_name

    ,u.emailAddress
    ,COUNT(IF(i.action_name = 'ADD', 1, NULL)) AS SCANS
FROM User_ AS u
INNER JOIN carts AS c ON c.userId = u.userId
INNER JOIN Impressions AS i ON i.session_id = c.session_id
WHERE i.createDate >= '2014-06-18'

    AND i.session_id <> ''
    AND c.cartId NOT IN 
    (SELECT cartId FROM cart_designers WHERE designId=102
    )
ORDER BY u.userId, c.session_id, c.cartId

我看到下表中重复的 ADD 搞乱了 SCANS 计数。

+---------+------------+---------+---------------+-------------+-----------------+
| user_id | session_id | cart_id | product_id    | action_name | email           |
+---------+------------+---------+---------------+-------------+-----------------+
| 1       | 1aaaa      | 1       | 1             | ADD <==problem| first@mail.org  |
| 1       | 1aaaa      | 1       | 1             | ADD <= problem| first@mail.org  |
| 1       | 1aaaa      | 1       | 6             | ADD           | first@mail.org  |
| 1       | 1aaaa      | 4       | 1             | ADD           | first@mail.org  |
| 1       | 1aaaa      | 4       | 6             | ADD <== okay  | first@mail.org  |
| 1       | 1cccc      | 6       | 8             | ADD           | first@mail.org  |
| 2       | 2ffff      | 2       | 5             | SENDMAIL      | second@mail.org |
| 2       | 2ffff      | 2       | 2             | ADD           | second@mail.org |
| 3       | 3bbbb      | 5       | 9             | ADD           | third@mail.org  |
| 3       | 3bbbb      | 5       | 10            | SENDMAIL      | third@mail.org  |
| 3       | 3dddd      | 3       | 3             | SENDMAIL      | third@mail.org  |
| 3       | 3dddd      | 3       | 4             | ADD           | third@mail.org  |
| 3       | 3dddd      | 3       | 7             | REMOVE        | third@mail.org  |
+---------+------------+---------+---------------+-------------+-----------------+

Answer 1

SELECT DISTINCT u.userId ,
                c.session_id ,
                c.cartId ,
                i.product_Id ,
                i.action_name ,
                u.emailAddress
FROM User_ AS u
INNER JOIN carts AS c ON c.userId = u.userId
INNER JOIN Impressions AS i ON i.session_id = c.session_id
WHERE i.createDate >= '2014-06-18'
  AND i.session_id <> ''
  AND c.cartId NOT IN
    (SELECT cartId
     FROM cart_designers
     WHERE designId=102 )
ORDER BY u.userId,
         c.session_id,
         c.cartId

我想添加DISTINCT会有帮助。它会删除 CART 中特定 user_id、session_id、product_id、action_name、email_id 的所有重复项。由此您可以计算 SCANS。