Schema（MySQL v8.0）

CREATE TABLE customers (
    CustomerId SERIAL PRIMARY KEY,
    Country VARCHAR(255),
    Fruit INTEGER,
    Veg INTEGER,
    Meat INTEGER
);

INSERT INTO customers (CustomerId, Country, Fruit, Veg, Meat) VALUES
(1, 'Spain', 100, 25, 60),
(2, 'USA', 20, 50, 150),
(3, 'India', 25, 20, 15),
(4, 'Germany', 35, 46, 75),
(5, 'Spain', 22, 36, 50),
(6, 'USA', 12, 34, 56),
(7, 'India', 67, 22, 38),
(8, 'Germany', 21, 45, 18);

查询

with cus as (
  select 
    distinct country, 
    sum(fruit) over(partition by country) as fruit, 
    sum(veg) over (partition by country) as veg, 
    sum(meat) over (partition by country) as meat 
  from 
    customers
) 
select 
  country, 
  case when fruit > veg 
  AND fruit > meat THEN 'fruit' WHEN veg > fruit 
  AND veg > meat THEN 'veg' WHEN meat > fruit 
  AND meat > veg THEN 'meat' end as popular_product 
from 
  cus;

| 国家|流行产品|
| --|--|
| 德国|肉|
| 印度|水果|
| 西班牙|水果|
| 美国|肉|
View on DB Fiddle