Question

我的桌子看起来像：

hook_bait  | brand    | flavour
----------------------------------
corn       |          |
bread      |          |
boily      | variable | variable

我需要运行一个mysql查询，它会给我最流行的hook_bait的名称，以及如果是boily-最流行的boily是什么品牌和口味。

我不知道从哪里开始

Answer 1

您可以通过以下查询获得最流行的hook_bait：

SELECT 
  hook_bait, 
  COUNT(hook_bait) AS popularity
FROM sample_table
GROUP BY hook_bait
ORDER BY popularity DESC
LIMIT 0, 1

完整的示例查询：

SELECT 
  hook_bait, 
  IF(hook_bait =  boily , (SELECT brand FROM sample_table t2 WHERE t2.hook_bait = t1.hook_bait GROUP BY brand ORDER BY COUNT(brand) DESC LIMIT 1),  - ) AS brand,
  IF(hook_bait =  boily , (SELECT flavour FROM sample_table t2 WHERE t2.hook_bait = t1.hook_bait GROUP BY flavour ORDER BY COUNT(flavour) DESC LIMIT 1),  - ) AS flavour
FROM sample_table t1
GROUP BY hook_bait
ORDER BY COUNT(hook_bait) DESC
LIMIT 0, 1

友情链接