php - MySQL:将表连接到自身，消除重复行

Question

我有一个将表连接到其自身的查询。结果包含重复的行(某种程度上)。此查询的目的是生成最常一起购买的产品列表。考虑这个查询:

SELECT o1.ITEM
,o2.ITEM as ITEM2
,o3.ITEM AS ITEM3
,count(DISTINCT o1.ORDERNUM) as oCount
FROM orders o1
INNER JOIN orders o2 ON o2.ORDERNUM = o1.ORDERNUM AND o2.ITEM != o1.ITEM
LEFT OUTER JOIN orders o3 ON o3.ORDERNUM = o1.ORDERNUM AND o3.ITEM != o2.ITEM AND o3.ITEM != o1.ITEM
GROUP BY o1.ITEM, o2.ITEM, o3.ITEM
ORDER BY oCount DESC

前 12 个结果:

+-------------+-------------+-------------+--------+
| ITEM        | ITEM2       | ITEM3       | oCount |
+-------------+-------------+-------------+--------+
| 02B13.04.GP | 77A04.10    | 45A04.04.GP |     54 |
| 02B13.04.GP | 45A04.04.GP | 77A04.10    |     54 |
| 77A04.10    | 45A04.04.GP | 02B13.04.GP |     54 |
| 45A04.04.GP | 02B13.04.GP | 77A04.10    |     54 |
| 77A04.10    | 02B13.04.GP | 45A04.04.GP |     54 |
| 45A04.04.GP | 77A04.10    | 02B13.04.GP |     54 |
| 57B01.01.GP | 57B01.11.GP | 57B01.10.GP |     12 |
| 57B01.10.GP | 57B01.11.GP | 57B01.01.GP |     12 |
| 57B01.01.GP | 57B01.10.GP | 57B01.11.GP |     12 |
| 57B01.10.GP | 57B01.01.GP | 57B01.11.GP |     12 |
| 57B01.11.GP | 57B01.10.GP | 57B01.01.GP |     12 |
| 57B01.11.GP | 57B01.01.GP | 57B01.10.GP |     12 |

请注意，前 6 个结果是相同的连接，但顺序不同。后 6 个结果也存在同样的问题(并且这种情况在整个结果中持续存在)。我的目标是为每个项目组创建一条记录，而不是为每个项目组的每个组合创建一行记录。

如何避免这些重复的结果？

此外，欢迎提供有关更有效地执行此查询的方法的任何建议(我想添加一个额外的联接，但对于 1,000,000 个订单，资源需求正在失控)。

================================================== =

编辑:回答Darshan的问题

你能分享一下表结构吗:

该表包含所有订单的行。如果订单包含多个产品，则每个产品都会有一行(给定订单有多个行)。此查询中唯一值得关注的列是:

ORDERNUM CHAR : Order Number
ITEM CHAR : SKU for the item
QTY INT : Quantity purchased
ORDDATE DATETIME : Order Date

返回的结果:我所需要的就是上面结果示例中列出的内容。目标是获取最常一起购买的产品列表。

Answer 1

你想要做的是消除重复的行，无论位置如何；一个技巧是根据 item1 < item2 < item3

的谓词来过滤结果，因为你总是拥有所有项目的组合

这是一个可能的解决方案:

SELECT a.item, b.item, c.item, count(*)
from `orders` a left join orders b
on a.ordernum = b.ordernum and a.item <> b.item
left join orders c on a.ordernum = c.ordernum
and a.item <> c.item and b.item <> c.item
where a.item < b.item and b.item < c.item
group by a.item, b.item, c.item
order by count(*) desc