mysql - 当在 mysql 5.6 严格 sql 模式下不起作用时，按情况分组

Question

我在 MySql 服务器上运行的查询遇到问题。这是查询:

SELECT itms.Gender,itms.Age, (CASE WHEN (plv.Discount = 0 OR t1.EverGreen = 1) THEN 'A' ELSE 'S' END) as SCode, itms.SubLineID, min(t1.DayDelivery)
FROM item as t1 
INNER JOIN item_contest_summary as itms 
    on (t1.SeasonID=itms.SeasonID and t1.CollectionID=itms.CollectionID and t1.ItemID=itms.ItemID and itms.JoinType<2) 
INNER JOIN pricelist_summary as plc 
    on (plc.CountryID = 'IT' AND plc.SeasonID = itms.PriceSeasonID AND plc.CollectionID = itms.PriceCollectionID AND plc.ItemID = itms.PriceItemID ) 
INNER JOIN pricelist AS plv 
    on (plv.ListCode = plc.ListCode AND plv.SeasonID = itms.PriceSeasonID AND plv.CollectionID = itms.PriceCollectionID AND plv.ItemID = itms.PriceItemID AND (plv.ListPrice > 0 or itms.IsGAG))    
WHERE itms.SCode IN ('S', 'A') AND itms.Age in ('A','K','') AND t1.HasPrivateSale = 0 AND itms.ExclLista = 0 AND itms.IsGAG = 0 
GROUP BY itms.Gender,itms.Age, 
(CASE WHEN (plv.Discount = 0 OR t1.EverGreen = 1) THEN 'A' ELSE 'S' END), 
itms.SubLineID 

当我们在服务器上禁用严格sql(版本5.1)时，该语句曾经工作正常，然后我们将系统升级到5.6，启用严格sql模式，问题就来了。此查询的问题似乎是 case when 部分中的表达式:

(plv.Discount = 0 OR t1.EverGreen = 1)

仅使用 OR 中的两个表达式之一，一切正常(保留第一个和第二个表达式)。似乎不可能在 case when 子句使用的表达式内使用任何 AND/OR/等。为什么？还有其他方法可以编写这种查询吗？

编辑:

这是我收到的响应错误:

Error Code: 1055. 'plv.Discount' isn't in GROUP BY

Answer 1

我发现使用别名进行分组效果非常好。因此，工作查询是:

SELECT itms.Gender,itms.Age, 
    (CASE WHEN (plv.Discount = 0 OR t1.EverGreen = 1) THEN 'A' ELSE 'S' END) as SCode, 
    itms.SubLineID , min(t1.DayDelivery)
FROM item as t1 
INNER JOIN item_contest_summary as itms 
    on (t1.SeasonID=itms.SeasonID and t1.CollectionID=itms.CollectionID and t1.ItemID=itms.ItemID and itms.JoinType<2) 
INNER JOIN pricelist_summary as plc 
    on (plc.CountryID = 'IT' AND plc.SeasonID = itms.PriceSeasonID AND plc.CollectionID = itms.PriceCollectionID AND plc.ItemID = itms.PriceItemID ) 
INNER JOIN pricelist AS plv 
    on (plv.ListCode = plc.ListCode AND plv.SeasonID = itms.PriceSeasonID AND plv.CollectionID = itms.PriceCollectionID AND plv.ItemID = itms.PriceItemID AND (plv.ListPrice > 0 or itms.IsGAG))    
WHERE itms.SCode IN ('S', 'A') AND itms.Age in ('A','K','') AND t1.HasPrivateSale = 0 AND itms.ExclLista = 0 AND itms.IsGAG = 0 
GROUP BY itms.Gender,itms.Age, 
    SCode, 
    itms.SubLineID 

我仍然不明白为什么只有在 case when 语句中使用复杂表达式时才需要这样做。此外，即使 select 上没有聚合函数，此版本的查询也能正常工作。问题解决了，但还是很困惑...