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

假設表名是記錄， 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

雷雷

Answer 5

要數數（不同的好名字）

Answer 6

雷雷

Answer 7

雷雷

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 with cnt >; 2;

Answer 10

雷雷