选择联接上的不同记录

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

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