如何在没有聚合和分组的MySQL中找到最多两个条目

I have a table Transactions(store_id, item_id, price). I want to find store_id's which sell at most two different item without using aggregate functions and groupings.

我有一个表事务(store_id、item_id、price)。我希望找到store_id，它最多可以销售两种不同的商品，而不需要使用聚合函数和分组。

Is there any way to do that ?

有什么办法吗?

1 个解决方案

#1

Interesting requirements... this would be a lot faster and easier with aggregate functions and groupings.. but here's another way:

有趣的需求……使用聚合函数和分组，这将会更快、更容易。但这是另一种方式:

SELECT DISTINCT t1.store_id
FROM 
    Transactions t1 
    LEFT JOIN Transactions t2 
        ON t1.store_id = t2.store_id 
        AND t1.item_id <> t2.item_id
    LEFT JOIN Transactions t3 
        ON t1.store_id = t3.store_id 
        AND t3.item_id NOT IN (t1.item_id, t2.item_id)
WHERE t3.store_id IS NULL

The query works by joining from one store record to another record for the same store, but different item. It then attempts to join to a third record for the same store, but different item. If it finds this record, then the store sells more than two items, and will be excluded in the WHERE clause.

查询通过从一个存储记录连接到同一存储的另一个记录，但是是不同的项来工作。然后，它尝试连接到同一存储的第三条记录，但是是不同的项。如果找到此记录，则该商店将销售超过两项，并将被排除在WHERE子句中。

Just to give you an idea, here's how the query would normally look:

给你一个概念，以下是查询通常的样子:

SELECT store_id
FROM Transactions
GROUP BY store_id
HAVING COUNT(DISTINCT item_id) < 3

#1