蜂巢 - 从选择组数据包含

Question

假设我有一个表在蜂巢像这样至少有一行：

|Id|Data |Data2 |Groupkey| 
|1 |One |  |Group1 | 
|2 |Two |Stuff |Group1 | 
|3 |Shoes|Some |Group2 | 
|4 |four |Stuff |Group2 | 
|5 |Three|Notme |Group3 | 

对于包含“东西”在Data2我想有该行各组来自'Stuff'行的Stuff和Data2以外的行中的Groupkey和Data。

所以得出的数据集看起来像

|Group |Data |Data2| 
|Group1|One |Two | 
|Group2|Shoes|four | 

我希望得到的东西与GROUP BY去，我开始与

SELECT Data, Groupkey FROM (SELECT Data, GroupKey FROM MyTable GROUP BY Groupkey) WHERE Data2 <> 'Stuff'去，但这个失败提示我需要包括在数据分组，但那不是我想要分组的？

而我不知道如何选择只包含一行与特定数据的组。

Answer 1

select  Groupkey           as `Group` 
      ,min (case when Data2 <> 'Stuff' then Data end)  as Data 
      ,min (case when Data2 = 'Stuff' then Data end)  as Data2 

from  MyTable 

group by Groupkey 

having  count (case when Data2 = 'Stuff' then 1 end) > 0 
; 

---

+--------+-------+-------+ 
| group | data | data2 | 
+--------+-------+-------+ 
| Group1 | One | Two | 
| Group2 | Shoes | four | 
+--------+-------+-------+ 

Answer 2

SELECT DISTINCT Groupkey, t1.Data, t2.Data as Data2 
FROM t t1 
INNER JOIN t t2 
ON t1.Groupkey = t2.Groupkey 
AND t1.Data2 <> t2.Data2 
WHERE t2.Data2 = 'Stuff'