hadoop - 发票、颜色、类型、计数

Question

我有一个下表:

Invoice| Colour | Type

In_001 |  Red   | D  
In_001 |  Red   | D  
In_001 | Yellow | E  
In_002 |  Red   | H

我想要的结果是整理出任何超过
购买了两件商品并显示数量。

见下文，期望的结果:

Invoice | Colour | Type | Count

In_001  |  Red   |  D   | 2  
In_001  | Yellow |  E   | 1

备注:In_002 不包括在内，因为它只购买了一件商品。

我尝试了以下命令:

select invoice, colour, type, count(invoice) from t   
group by invoice,colour,type   
having count(invoice)>1;

结果是:

Invoice | Colour | Type | Count

In_001  |  Red   |  D   | 2

请帮忙。

Answer 1

分组依据是所有三个发票、颜色和类型。所以结果只会是这样。
我们可以尝试以下查询来实现它:-

select invoice,colour,type,count(invoice) from t where invoice in
(select invoice from t group by invoice having count(invoice) >1) 
group by invoice,colour,type