MySQL-如果在其他表中出现超过x次，则选择行 [英] MySQL - Select row if appear more than x times in other table

问题描述

让我们说我有两个表，成员"和订单"具有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

如果会员在订单"表中有多个订单，我正在尝试从会员"表中获取会员ID".

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.

所以结果应该是...

So the result should be...

Members
UserID  Name
111     Peter
333     Joe

...因为彼得和乔在订单"表中都至少有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
    )

但是那给了我Peter 3次，Joe 2次.所以我得到重复而不是每个UserID一次.如何获得没有重复的结果?

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

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

这篇关于MySQL-如果在其他表中出现超过x次，则选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！