嵌套MySQL GROUP BY命令

Question

我正在尝试使用GROUP_BY命令的层次结构创建MySQL查询。我想要一张桌子，并按水果和颜色列进行分组。

然后，我想创建一个新列“new？”，它查看水果的所有实例的DATE列(因此只按水果分组--不管颜色如何)，并检查该水果的所有实例的Date = 3.6.15。如果是这样，则将"Yes“分配给该水果的所有行；否则，将"No”分配给该水果的所有行。

我的表当前如下所示：

FRUIT    COLOR    DATE    
Apple    Red      3.6.15
Apple    Green    3.6.15
Pear     Yellow   3.2.15
Melon    Red      3.6.15
Melon    Red      3.2.15
Grapes   Purple   3.6.15
Grapes   Green    3.4.15

下面是我想要创建的新视图：

FRUIT    COLOR    NEW?  
Apple    Red      Yes
Apple    Green    Yes
Pear     Yellow   No
Melon    Red      No
Grapes   Purple   No
Grapes   Green    No

对我如何实现这一目标的任何帮助都将不胜感激。

Answer 1

您可以在case语句中使用聚合：

select fruit, color,
       (case when exists (select 1 from table t2 where t2.color = t.color and date <> '2015-03-06')
             then 'no'
             else 'yes'
        end) as IsNew
from table t;

这只会检查不是当前日期的日期。记录应该存在，因为子查询在同一个表上。

Answer 2

您可以使用self join来完成此操作。连接表将为具有多个日期的所有水果返回一个非空值，case表达式使用该值来确定该水果是否为新水果：

select 
    t1.FRUIT, 
    t1.COLOR,
    case when t1.date = '3.6.15' and t2.DATE is null 
      then 'Yes' 
      else 'No' 
    end as "New?"
from fruits t1
left join fruits t2 on t1.FRUIT = t2.FRUIT and t1.DATE <> t2.DATE;

Sample SQL Fiddle