MySQL 在连接上选择不同的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2068515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select distinct records on a join
提问by George
I have two mysql tables - a sales table:
我有两个 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 | |
+----------------+------------------------------+------+-----+---------+-------+
and an items table:
和一个项目表：
+--------------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------------------+------+-----+---------+-------+
| ItemId | bigint(20) unsigned | NO | PRI | NULL | |
| ItemName | varchar(100) | NO | | NULL | |
+--------------------+------------------------------+------+-----+---------+-------+
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:
sales 表包含每个ItemID 的多个记录- 每个SaleWeek 一个。我想通过加入两个表来选择所有出售的物品，如下所示：
SELECT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;
However, this is returning multiple ItemIdvalues 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 SaleWeekbecause some items may not have an entry for the latest SaleWeekso I need to get the last sale. Do I need to specify DISTINCTor use a LEFT OUTER JOINor something?
但是，这会根据每个SaleWeek的多个条目返回多个ItemId值。我可以做一个不同的选择来只返回一个ItemID- 我不想查询最新的SaleWeek，因为有些项目可能没有最新SaleWeek的条目，所以我需要获得最后一次销售。我是否需要指定DISTINCT或使用LEFT OUTER JOIN或其他什么？
回答by Kaleb Brasee
A DISTINCTshould do what you're looking for:
ADISTINCT应该做你正在寻找的：
SELECT DISTINCT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;
That would return only distinct items.ItemName, items.ItemIdtuples.
那只会返回不同的items.ItemName, items.ItemId元组。
回答by DRapp
YOu had comment about the sales week too. And wanting the most recent week, you may want to try using a GROUP BY
你也对销售周发表了评论。想要最近一周，您可能想尝试使用 GROUP BY
SELECT
items.ItemName,
items.ItemId,
max( Sales.SaleWeek ) MostRecentSaleWeek
FROM
items JOIN sales ON items.ItemId = sales.ItemId
WHERE
sales.StoreID = ?
GROUP BY
items.ItemID,
items.ItemName
ORDER BY
MostRecentSaleWeek, -- ordinal column number 3 via the MAX() call
items.ItemName
You may have to change the ORDER BY to the ordinal 3rd column reference if you so want based on that column.. This query will give you each distinct item AND the most recent week it was sold.
如果您希望基于该列，您可能必须将 ORDER BY 更改为第 3 列引用。此查询将为您提供每个不同的项目及其最近一周的销售情况。
回答by Suresh Kumar Amrani
SELECT u.user_name,u.user_id, u.user_country,u.user_phone_no,ind.Industry_name,inv.id,u.user_email
FROM invitations inv
LEFT JOIN users u
ON inv.sender_id = u.user_id
LEFT JOIN employee_info ei
ON inv.sender_id=ei.employee_fb_id
LEFT JOIN industries ind
ON ei.industry_id=ind.id
WHERE inv.receiver_id='XXX'
AND inv.invitation_status='0'
AND inv.invitati
on_status_desc='PENDING'
GROUP BY (user_id)
回答by sivaprakash
We can use this:
我们可以这样使用：
INSERT INTO `test_table` (`id`, `name`) SELECT DISTINCT
a.`employee_id`,b.`first_name` FROM `employee_leave_details`as a INNER JOIN
`employee_register` as b ON a.`employee_id` = b.`employee_id`
