mysql - 在SQL中，选择计数超过给定数字的列的唯一元素

Question

假设我有一个“订单”表，如下所示:

我已将其输入到 SQLfiddle ( http://sqlfiddle.com/#!9/b9d7a/6) 中，如下所示:

CREATE TABLE Orders
    (`Number` int, `order_date` varchar(10), `cust_id` int, `salesperson_id` int, `Amount` int)
;

INSERT INTO Orders
    (`Number`, `order_date`, `cust_id`, `salesperson_id`, `Amount`)
VALUES
    (10, '8/2/96', 4, 2, 540),
    (20, '1/30/99', 4, 8, 1800),
    (30, '7/14/95', 9, 1, 460),
    (40, '1/29/98', 7, 2, 2400),
    (50, '2/3/98', 6, 7, 600),
    (60, '3/2/98', 6, 7, 720),
    (70, '5/6/98', 9, 7, 150)
;

我想选择多次出现的 salesperson_id 元素。到目前为止，我已经完成了

SELECT
  salesperson_id
FROM
  Orders
GROUP by salesperson_id

但这会导致选择 salesperson_id 的所有唯一元素 - 即 1、2、7 和 8 - 而不仅仅是出现多次的那些，即 2 和 7。有什么办法可以GROUP by 指定最小计数吗？

Answer 1

SELECT salesperson_id, count(salesperson_id) as c
FROM Orders 
GROUP by salesperson_id 
HAVING c>1

甚至更短

SELECT salesperson_id FROM Orders GROUP by salesperson_id HAVING count(salesperson_id)>1