MySQL - 如果在其他表中出现超过 x 次，则选择行

2021-12-30 00:00:00 join sql count mysql

假设我有两个表，成员和订单具有 1:N 关系:

Lets say I have two tables, Members and Orders with a 1:N relation:

Members         | Orders
UserID  Name    | OrderID   UserID  Name
111     Peter   | 777       111     Peter
222     Bart    | 888       333     Joe
333     Joe     | 999       111     Peter
444     Andrew  | 101       444     Andrew
                | 102       111     Peter
                | 103       333     Joe

我正在尝试从Members 表中获取Members ID，以防Member 在Orders 表中有超过1 个订单.

I am trying to get the Members ID from the Members table, in case that Member has more than 1 order in the Orders table.

所以结果应该是...

Members
UserID  Name
111     Peter
333     Joe

...因为 Peter 和 Joe 在 Orders 表中都至少有 2 个订单.

...because Peter and Joe both have at least 2 orders in the Orders table.

我试图得到一个结果:

SELECT
    s.UserID,
FROM Members s
    INNER JOIN Orders o
        ON s.UserID = o.UserID
WHERE
    s.UserID IN
    (
        SELECT UserID
        FROM Orders
        GROUP BY UserID
        HAVING COUNT(*) > 5
    )

但这让我回到彼得 3 次，乔 2 次；所以我得到重复而不是每个用户 ID 一次.如何获得没有重复的结果?

But that gives me Peter back 3 times, and Joe 2 times; so I get duplicates instead of each UserID once. How can I get the result without duplicates?

推荐答案

我假设(并希望)您不会将用户名存储两次，因为这会在用户更改其名称时导致数据质量问题.

I'm assuming (and hoping) that you're not storing the user's name twice, since that leads to data quality issues when the user changes their name.

假设表格的结构如下:

CREATE TABLE
  Members
(
    UserID INT,
    Name VARCHAR(15)
);

INSERT INTO
  Members
VALUES
(111, 'Peter'),
(222, 'Bart'),
(333, 'Joe'),
(444, 'Andrew');

CREATE TABLE
  Orders
(
   OrderID INT,
   UserID INT
);

INSERT INTO
  Orders
VALUES
(777, 111),
(888, 333),
(999, 111),
(101, 444),
(102, 111),
(103, 333);

您可以使用 GROUP BY 和 HAVING 子句，它们将为您提供超过 1(或任意数字)的所有用户的 UserID您选择)订单.然后，您将其加入 Members 表以获取名称.

You can use a GROUP BY and HAVING clause which would give you the UserID of all users with more than 1 (or whichever number you choose) orders. Then, you join that to the Members table to get the name.

SELECT
  Orders.UserID,
  Members.Name
FROM
  Orders
INNER JOIN
  Members
  ON Orders.UserID = Members.UserID
GROUP BY
  UserID,
  Members.Name
HAVING
  COUNT(OrderID) > 1;

SQLFiddle:http://sqlfiddle.com/#!9/1dadc4/2

但是，如果您已经存储了名称(并且没有更改)，那么您可以跳过 JOIN 如下所示:

However, if you already have the names stored (and that's not changing), then you could skip the JOIN like below:

SELECT
  UserID,
  Name
FROM
  Orders
GROUP BY
  UserID,
  Name
HAVING
  COUNT(OrderID) > 1

相关文章