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

goods_name 不同，才代表是不同款的产品吧。购买2款以上，和购买某款产品的num是2以上，没有什么关系吧。
楼上的回答都不对。

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

躺被窝写的，大概这个意思。

不好意思，上次没仔细看你的题，也没动脑子随便给了个想法。这次亲测可行，祝楼主工作顺利。

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

第一种：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;
改进版的答案：
select username,count(DISTINCT(goods_name)) as nums from shop group by username having nums>1;
在我的追加评论里面也有

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