在连接上选择不同的记录

本文介绍了在连接上选择不同的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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