mysql按列名获取结果组[重复]

2 回答

• 0

  SELECT city
      CASE        
       WHEN type = 'Medical' THEN amount END) Medical,  
       WHEN type = 'Educational' THEN amount END) Educational,   
       WHEN type = 'Scholarship' THEN amount END) Scholarship   
  FROM tbl_client
  GROUP BY city
  

  回复于 2024-05-06T16:59:08+08:00
• 0

  您可以尝试使用条件加重函数 SUM 与 GROUP BY

  Schema (MySQL v5.7)

  CREATE TABLE T(
     c_ID int,
     city varchar(50),
     type varchar(50),
     amount int
  );
  
  
  
  
  INSERT INTO T VALUES (1,'Boston','Medical',1500);
  INSERT INTO T VALUES (2,'Miami','Educational',3000);
  INSERT INTO T VALUES (3,'Dallas','Scholarship',1000);
  INSERT INTO T VALUES (4,'Boston','Scholarship',1500);
  INSERT INTO T VALUES (5,'Miami','Medical',3000);
  INSERT INTO T VALUES (6,'Boston','Educational',1000);
  INSERT INTO T VALUES (7,'Miami','Medical',3000);
  INSERT INTO T VALUES (8,'Dallas','Medical',1000);
  

  ---

  Query #1

  SELECT city,          
      SUM(CASE WHEN type = 'Medical' THEN amount END) Medical,  
      SUM(CASE WHEN type = 'Educational' THEN amount END) Educational,   
      SUM(CASE WHEN type = 'Scholarship' THEN amount END) Scholarship   
  FROM T 
  GROUP BY city;
  
  | city   | Medical | Educational | Scholarship |
  | ------ | ------- | ----------- | ----------- |
  | Boston | 1500    | 1000        | 1500        |
  | Dallas | 1000    |             | 1000        |
  | Miami  | 6000    | 3000        |             |
  

  ---

  View on DB Fiddle

  回复于 2024-05-06T16:59:08+08:00