MySQL 使用 CASE AND Group By 进行选择

Question

我有以下 MySQL 语句，它从 3 个表中收集数据。

这些表具有以下结构:

旅行计划

#   Name        Type            
1   id          int(11)         
2   plan        varchar(11)         
3   created_at  timestamp               
4   updated_at  timestamp       
5   created_by  int(11)                 
6   updated_by  int(11)             
7   eligibility varchar(50)     
8   lower_limit int(11)             
9   upper_limit int(11)     

计划类别

#   Name        Type    
1   idPrimary   int(11)         
2   category    varchar(255)            
3   created_at  timestamp                   
4   updated_at  timestamp       
5   updated_by  int(11)             
6   created_by  int(11) 

计划类别详细信息

#   Name                Type    
1   idPrimary           int(11) 
2   plan_category_id    int(11)         
3   travel_plan_id      int(11)     
4   description         varchar(255)        
5   created_at          timestamp           
6   updated_at          timestamp
7   created_by          int(11)     
8   updated_by          int(11) 

这是我的 SQL 查询:

SELECT 
  plan_category.id,
  plan_category.category AS OPTION,
  CASE
    WHEN plan = 'Business' THEN plan_category_detail.description
    ELSE ''
  END AS Business,
  CASE
    WHEN plan = 'Holiday' THEN plan_category_detail.description
    ELSE ''
  END AS Holiday,
  CASE
    WHEN plan = 'Schengen' THEN plan_category_detail.description
    ELSE ''
  END AS Schengen,
  CASE
    WHEN plan = 'Student' THEN plan_category_detail.description
    ELSE ''
  END AS Student,
  CASE
    WHEN plan = 'Senior' THEN plan_category_detail.description
    ELSE ''
  END AS Senior,
  CASE
    WHEN plan = 'Incoming' THEN plan_category_detail.description
    ELSE ''
  END AS Incoming,
  CASE
    WHEN plan = 'East Africa' THEN plan_category_detail.description
    ELSE ''
  END AS East_Africa
FROM
  travel_plan
    INNER JOIN
  plan_category_detail ON plan_category_detail.travel_plan_id = travel_plan.id
    INNER JOIN
  plan_category ON plan_category.id = plan_category_detail.plan_category_id
GROUP BY plan_Category_detail.id , plan_category.id;

它给了我以下输出:

"1" "LIMIT (Medical Expenses and Related Expenses)" "300,000"   ""  ""  ""  ""  ""  ""
"1" "LIMIT (Medical Expenses and Related Expenses)" ""  "200,000"   ""  ""  ""  ""  ""
"1" "LIMIT (Medical Expenses and Related Expenses)" ""  ""  "80,000"    ""  ""  ""  ""
"1" "LIMIT (Medical Expenses and Related Expenses)" ""  ""  ""  "75,000"    ""  ""  ""
"1" "LIMIT (Medical Expenses and Related Expenses)" ""  ""  ""  ""  "100,000"   ""  ""
"1" "LIMIT (Medical Expenses and Related Expenses)" ""  ""  ""  ""  ""  "120,000"   ""
"1" "LIMIT (Medical Expenses and Related Expenses)" ""  ""  ""  ""  ""  ""  "30,000"

但是，我想要以下类型的输出:

"1" "LIMIT (Medical Expenses and Related Expenses)" "300,000"   "200,000"   "80,000"    "75,000"    "100,000"   "120,000"   "30,000"

如何实现上述输出？

我也尝试了GROUP BY功能，但仍然无法工作。请帮忙

Answer 1

这里需要一个条件聚合，因此只需在 case 表达式中使用 max()

    SELECT plan_category.id,plan_category.category AS OPTION,
           max(CASE WHEN plan ='Business' THEN plan_category_detail.description ELSE '' END) AS Business,
           max(CASE WHEN plan ='Holiday' THEN plan_category_detail.description ELSE '' END) AS Holiday,
           max(CASE WHEN plan ='Schengen' THEN plan_category_detail.description ELSE '' END) AS Schengen,
           max(CASE WHEN plan ='Student' THEN plan_category_detail.description ELSE '' END) AS Student,
           max(CASE WHEN plan ='Senior' THEN plan_category_detail.description ELSE '' END) AS Senior,
           max(CASE WHEN plan ='Incoming' THEN plan_category_detail.description ELSE '' END) AS Incoming,
           max(CASE WHEN plan ='East Africa' THEN plan_category_detail.description ELSE '' END) AS East_Africa
FROM travel_plan INNER JOIN plan_category_detail ON plan_category_detail.travel_plan_id = travel_plan.id 
INNER JOIN plan_category ON plan_category.id = plan_category_detail.plan_category_id  
GROUP BY plan_Category_detail.id , plan_category.id