oracle表中sql问题

Question

购买人 商品类型 数量
A      甲      1
B      乙      2
C      丙      2
A      丁      5
B      甲      3
1.使用sql求购买过俩种或者俩种以上的商品类型的购买人信息

Answer 1

create table shopping_record
(
consumer varchar2(5),
goods_typ VARCHAR2(5),
amount NUMBER);INSERT INTO shopping_record VALUES('A','jia',1);INSERT INTO shopping_record VALUES('B','yi',2);INSERT INTO shopping_record VALUES('C','bing',2);INSERT INTO shopping_record VALUES('A','ding',5);INSERT INTO shopping_record VALUES('B','jia',3);SELECT consumer
FROM shopping_record
GROUP BY consumer
HAVING COUNT(*)>1;

Answer 2

select 购买人 from 表 group by 购买人 having count(*)>1;  同意楼上的

Answer 3

SELECT consumer
FROM shopping_record
GROUP BY consumer
HAVING COUNT(goods_typ)>1;

Answer 4

select count(distinct 类型),购买人 from table_name group by 购买人having count(distinct类型) >= 2

Answer 5

  SELECT CONSUMER 
    FROM SHOPPING_RECORD 
GROUP BY CONSUMER 
  HAVING COUNT(DISTINCT(goods_typ)) > 1;

Answer 6

create table shopping_record
(
consumer varchar2(5),
goods_typ VARCHAR2(5),
amount NUMBER);INSERT INTO shopping_record VALUES('A','jia',1);INSERT INTO shopping_record VALUES('B','yi',2);INSERT INTO shopping_record VALUES('C','bing',2);INSERT INTO shopping_record VALUES('A','ding',5);INSERT INTO shopping_record VALUES('B','jia',3);select * from shopping_record a
where exists(select 1 from shopping_record b where a.consumer= b.consumer and a.goods_typ<> b.goods_typ)/*
CONSUMER GOODS_TYP AMOUNT                 
-------- --------- ---------------------- 
A        jia       1                      
B        yi        2                      
A        ding      5                      
B        jia       3                      4 rows selected
*/
--借用一楼脚本数据

Answer 7

我认为，应该对商品类型去重下会更好。
having字句变为，COUNT(distinct goods_typ)>1