我有两个MySQL表 - 一个销售表:
+----------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------------------+------+-----+---------+-------+
| StoreId | bigint(20) unsigned | NO | PRI | NULL | |
| ItemId | bigint(20) unsigned | NO | | NULL | |
| SaleWeek | int(10) unsigned | NO | PRI | NULL | |
+----------------+------------------------------+------+-----+---------+-------+
还有一个物品表:
+--------------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------------------+------+-----+---------+-------+
| ItemId | bigint(20) unsigned | NO | PRI | NULL | |
| ItemName | varchar(100) | NO | | NULL | |
+--------------------+------------------------------+------+-----+---------+-------+
销售表格包含每个 ItemID 的多条记录 - 每个 SaleWeek 一条。我想通过连接这两个表来选择所有已售出的物品,如下所示:
SELECT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;
然而,这将基于每个销售周的多个条目返回多个ItemId值。我是否可以执行一个DISTINCT选择来仅返回一个ItemID - 我不想查询最新的SaleWeek,因为某些项目可能没有最新的SaleWeek的条目,所以我需要获取最后一次销售。我需要指定DISTINCT还是使用LEFT OUTER JOIN或其他方法?