mysql - 请教一下这个SQL怎么写

Question

这是订单表信息

+----+----------+------------+------+
| id | username | goods_name | num  |
+----+----------+------------+------+
|  1 | Tom      | A          |    3 |
|  2 | Rose     | C          |    1 |
|  3 | Rose     | B          |    5 |
|  4 | Tom      | A          |    1 |
+----+----------+------------+------+

现在想找出购买两款以上产品的用户，请问SQL怎么写

各位亲们看清题目啊，是购买两款以上产品的用户，表里面Tom购买了两次，单他都购买的A产品，所有不能被查询出来的，只有Rose，他购买了C和B产品，所以他可以被查询出来

Answer 1

If the goods_name is different, it means they are different products. Buying more than 2 models has nothing to do with buying a product whose num is more than 2.
None of the answers above are correct.

select username, count(*) as cnt from t_tablename group by username, goods_name having cnt > 2;

Answer 2

select count(username) as number having number >=2

Written while lying on the bed, that’s what it means.

Sorry, I didn’t read your question carefully last time, and I didn’t use my brain to give you an idea. This personal test is feasible, I wish the author good luck in his work.

SELECT
*,
count(a.username) as buy_num
FROM
/*子表可以剔除购买同样商品的用户，然后根据用户名count就行了*/
    (
        SELECT
            *
        FROM
            你的表名
        GROUP BY
            goods_name
    ) AS a
GROUP BY a.username
HAVING buy_num>1

Answer 3

假设表名是record， select count(*) as name_count, sub.username from (select username, goods_name from record group by username, goods_name) as sub group by sub.username where sub.namecount ＞ 2

Answer 4

SELECT username,COUNT(goods_name) AS count FROM test GROUP BY goods_name HAVING count >= 2

Answer 5

要count(distinct good_name)

Answer 6

select username, count(1) as amount from (
select username, goods_name from odt group by username, goods_name
) as ogn
group by username
having amount>=2

Answer 7

SELECT username FROM table_name
    GROUP BY username
    HAVING DISTINCT(goods_name) >= 2;

Answer 8

The first one: select username,count(t.goods_name) as nums from (select DISTINCT(goods_name),username from shop) as t group by t.username having nums>1;
Improved answer:
select username,count(DISTINCT(goods_name)) as nums from shop group by username having nums>1;
Also in my additional comment

Answer 9

select username, count(*) as cnt from (select username from t group by username,goods_name) as a group by a.username having cnt > 2;

Answer 10

SELECT
    username,
    count(DISTINCT goods_name) AS c
FROM
    table_name
GROUP BY
    username
HAVING
    c > 1