将行与总和分组 [英] group rows into columns with sum

问题描述

假设我们有这张桌子:

CREATE TABLE `table1` (
`Name` CHAR(50) NOT NULL,
`Type` CHAR(50) NOT NULL,
`Value` DOUBLE NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


 ---------------------------------
|   Name   |   Type   |   Value   | 
 ---------------------------------   
|   Dep1   |   Cash   |   100     | 
 ---------------------------------  
|   Dep1   |   Card   |   200     | 
 ---------------------------------  
|   Dep2   |   Cash   |   300     | 
 ---------------------------------  
|   Dep2   |   Card   |   400     | 
 ---------------------------------

如何创建MySQL选择查询以转换像这样的列中的行? 我必须提到名称"列具有动态值！

How to create a MySQL select query to transform rows in columns like this? I must mention that column 'Name' has dynamic values!

 --------------------------------
|   Name   |   Cash   |   Card   |    
 --------------------------------   
|   Dep1   |    100   |    200   |     
 --------------------------------   
|   Dep2   |    300   |    400   |
 --------------------------------  

推荐答案

select name, 
       sum(case when `type` = 'Cash' then `value` else 0 end) as Cash,
       sum(case when `type` = 'Card' then `value` else 0 end) as Card
from your_table
group by name

这篇关于将行与总和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！