选择连接上的不同记录

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 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?

但是，这将根据每个SaleWeek的多个条目返回多个ItemId值。我可以做一个明确的选择只返回一个ItemID - 我不想要查询最新的SaleWeek，因为有些项目可能没有最新的SaleWeek的条目，所以我需要得到最后的销售。我是否需要指定DISTINCT或使用LEFT OUTER JOIN或其他东西？

4 个解决方案

#1

A DISTINCT should do what you're looking for:

DISTINCT应该做你想要的：

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.ItemId tuples.

这将只返回不同的items.ItemName，items.ItemId元组。

#2

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列引用。此查询将为您提供每个不同的项目以及销售的最近一周。

#3

    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)

#4

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`

#1