选择联接上的不同记录

问题描述

我有两个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    |       |
+--------------------+------------------------------+------+-----+---------+-------+

sales表包含每个 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.

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