在连接上选择不同的记录 [英] Select distinct records on a join

问题描述

我有两个 mysql 表 - 一个销售表:

I have two mysql tables - a sales table:

+----------------+------------------------------+------+-----+---------+-------+
| 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 的多个记录 - 每个 SaleWe​​ek 一个.我想通过加入两个表格来选择所有出售的商品，如下所示:

The sales table contains multiple records for each ItemID - one for each SaleWeek. I want to select all items sold by joining the two tables like so:

SELECT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId 
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

但是，这会根据每个 SaleWe​​ek 的多个条目返回多个 ItemId 值.我可以做一个不同的选择来只返回一个 ItemID - 我不想查询最新的 SaleWe​​ek，因为有些项目可能没有最新的条目SaleWe​​ek 所以我需要获得最后一次销售.我是否需要指定 DISTINCT 或使用 LEFT OUTER JOIN 或其他什么?

However, this is returning multiple ItemId values based on the multiple entries for each SaleWeek. Can I do a distinct select to only return one ItemID - I don't want to have to query for the latest SaleWeek because some items may not have an entry for the latest SaleWeek so I need to get the last sale. Do I need to specify DISTINCT or use a LEFT OUTER JOIN or something?

推荐答案

DISTINCT 应该可以满足您的需求:

A DISTINCT should do what you're looking for:

SELECT DISTINCT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId 
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

那只会返回不同的 items.ItemName, items.ItemId 元组.

That would return only distinct items.ItemName, items.ItemId tuples.

这篇关于在连接上选择不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！