MySQL中查询指定日期地点下最频繁食物及频次的方法

先看你的表结构及数据：

CREATE TABLE T (
  id VARCHAR(2),
  food VARCHAR(10),
  place VARCHAR(10),
  days VARCHAR(2)
);

INSERT INTO T VALUES
('i1', 'fruit', 'city', 'd1'),
('i2', 'meat', 'town', 'd2'),
('i3', 'bread', 'village', 'd3'),
('i4', 'fruit', 'city', 'd1'),
('i5', 'meat', 'city', 'd1'),
('i6', 'meat', 'city', 'd1'),
('i7', 'bread', 'village', 'd3'),
('i8', 'fruit', 'village', 'd3'),
('i9', 'bread', 'city', 'd1');

你的需求是查询特定日期、地点下出现频次最高的食物，同时展示食物、地点、日期及频次，而且不能用order by搭配limit——因为可能存在多个频次相同的并列结果。你之前尝试的SQL问题出在分组逻辑错误：group by place,days,foodcount会把相同频次的不同食物强行归为一组，导致丢失数据，而且max(foodCount)在这里没有实际意义。

下面分三种场景给出解决方案：

1. 仅查询指定日期+地点组合（比如d1,city） #

先统计该组合下各食物的频次，再找出最大频次，最后关联出对应的食物：

SELECT s.days, s.place, s.food, s.foodCount
FROM (
    SELECT days, place, food, COUNT(*) AS foodCount
    FROM T
    WHERE days = 'd1' AND place = 'city'
    GROUP BY days, place, food
) s
INNER JOIN (
    SELECT days, place, MAX(foodCount) AS maxCount
    FROM (
        SELECT days, place, COUNT(*) AS foodCount
        FROM T
        WHERE days = 'd1' AND place = 'city'
        GROUP BY days, place, food
    ) s1
    GROUP BY days, place
) m ON s.days = m.days AND s.place = m.place AND s.foodCount = m.maxCount;

执行后输出：

days    place       food    foodCount
d1      city        fruit   2
d1      city        meat    2

2. 查询所有有记录的日期+地点组合的最高频食物（忽略无记录的组合） #

去掉指定条件，覆盖所有存在数据的日期地点组合：

SELECT s.days, s.place, s.food, s.foodCount
FROM (
    SELECT days, place, food, COUNT(*) AS foodCount
    FROM T
    GROUP BY days, place, food
) s
INNER JOIN (
    SELECT days, place, MAX(foodCount) AS maxCount
    FROM (
        SELECT days, place, COUNT(*) AS foodCount
        FROM T
        GROUP BY days, place, food
    ) s1
    GROUP BY days, place
) m ON s.days = m.days AND s.place = m.place AND s.foodCount = m.maxCount;

执行后输出：

days    place       food    foodCount
d1      city        fruit   2
d1      city        meat    2
d2      town        meat    1
d3      village     bread   2

3. 查询所有日期+地点组合（包括无记录的，显示NULL） #

先生成所有可能的日期和地点的笛卡尔积，再关联统计数据：

-- 生成所有日期+地点的组合
WITH all_combinations AS (
    SELECT DISTINCT t1.days, t2.place
    FROM T t1
    CROSS JOIN T t2
),
-- 统计各组合下的食物频次
food_counts AS (
    SELECT days, place, food, COUNT(*) AS foodCount
    FROM T
    GROUP BY days, place, food
),
-- 找出每个组合的最大频次
max_counts AS (
    SELECT days, place, MAX(foodCount) AS maxCount
    FROM food_counts
    GROUP BY days, place
)
-- 关联所有组合和符合最大频次的食物
SELECT ac.days, ac.place, fc.food, fc.foodCount
FROM all_combinations ac
LEFT JOIN max_counts mc ON ac.days = mc.days AND ac.place = mc.place
LEFT JOIN food_counts fc ON ac.days = fc.days AND ac.place = fc.place 
    AND (mc.maxCount IS NULL OR fc.foodCount = mc.maxCount)
ORDER BY ac.days, ac.place;

执行后输出所有日期地点组合，无记录的组合food和foodCount显示为NULL：

days    place       food    foodCount
d1      city        fruit   2
d1      city        meat    2
d1      town        NULL    NULL
d1      village     NULL    NULL
d2      city        NULL    NULL
d2      town        meat    1
d2      village     NULL    NULL
d3      city        NULL    NULL
d3      town        NULL    NULL
d3      village     bread   2

内容的提问来源于stack exchange，提问作者Questionerer