sql行转列-case when+集合聚合函数的用法-oracle

case when和python里的if...elif类似，对比着看

            sql语法                                                      python语法

            case when 表达式                                      if   表达式

            Then 输出                                                  输出

             when 表达式                                              elif  表达式

             Then 输出                                                   输出

              Else                                                            else

                     输出                                                    输出

             End           

 

例子

             sql                                                           python

            Case when id = 1                                     if  id == 1:

                    Then 2                                                     2

                    When id = 2                                    elif  id == 2:

                    Then 1                                                    1

                    else                                                      else:

                    10                                                         10

                End       

python的冒号在sql里面用Then代替         

建表语句

Create table stu (id int, Name varchar(30),classid int)
insert into stu(id,Name,classid) values(1,'A',1)
insert into stu(id,Name,classid) values(2,'B',1)
insert into stu(id,Name,classid) values(3,'C',2)
insert into stu(id,Name,classid) values(4,'D',3)
insert into stu(id,Name,classid) values(5,'E',2)
insert into stu(id,Name,classid) values(6,'F',3)

 sql里的case when的例子2

需求把左图变成右图,classid列的1变2,2变1,3不变

 

id	Name	classid
1	A	1
2	B	1
3	C	2
4	D	3
5	E	2
6	F	3

 

                 

                    

                    

                   

                    

                  

将1班的学生转到2班，将2班的学生转到1班，将3班的学生不变,想要输出的结果为

id	Name	classid
1	A	2
2	B	2
3	C	1
4	D	3
5	E	1
6	F	3

 

            

                 

                   

                    

                    

                       

SQL语句          

SELECT id,Name,                                                                                
        (case when classid = 1 Then  2                                                                                                                                           
              when  classid = 2 Then  1                                                                                                                                                                                   
              else                                                                                                       
                   classid  End) AS a                                                                                 
 FROM stu;

 这里case when作列名太长了，用一个别名a代替，case when是放到select语句里面的

case when部分对应Python

   if  classid == 1:
        2
   elif classid == 2:
        1
   else:
         classid

case when相当于python的if

python的elif对应mysql里的when

二    聚集函数 ＋ case when（行转列）

建表语句

Create table scores(Name varchar(30), stage varchar(30),score int)
insert into scores(Name,stage,score) values('A','基础学科',1)
insert into scores(Name,stage,score) values('B','基础学科',2)
insert into scores(Name,stage,score) values('C','基础学科',3)
insert into scores(Name,stage,score) values('A','爬虫',1)
insert into scores(Name,stage,score) values('B','爬虫',3)
insert into scores(Name,stage,score) values('C','爬虫',2)
insert into scores(Name,stage,score) values('A','SQL',2)
insert into scores(Name,stage,score) values('B','SQL',2)
insert into scores(Name,stage,score) values('C','SQL',4)

　　需求把左图变成右图

 

  表   分数 scores

Name	stage	score
A	基础学科	1
B	基础学科	2
C	基础学科	3
A	爬虫	1
B	爬虫	3
C	爬虫	2
A	SQL	2
B	SQL	2
C	SQL	4

 

                      

                      

                        

                       

                  

                    

                       

                    

                         

 需求通过一个SQL语句把上诉的表打印出一下的效果

SELECT name,
       max(CASE WHEN stage='基础学科' THEN score ELSE NULL END) AS 基础学科,
       max(CASE WHEN stage='爬虫' THEN score ELSE NULL END) AS 爬虫,
       max(CASE WHEN stage='SQL' THEN score ELSE NULL END) AS SQL
FROM scores GROUP BY name;

　　

Name	基础学科	爬虫	SQL
A	1	1	2
B	2	3	2
C	3	2	4

 

                   

             

                

怎么把3列的表变成4列(解析)

Name	学科	成绩	列1	列2	列3
A	基础学科	1	1	null	null
B	基础学科	2	2	null	null
C	基础学科	3	3	null	null
A	爬虫	1	null	1	null
B	爬虫	3	null	3	null
C	爬虫	2	null	2	null
A	SQL	2	null	null	2
B	SQL	2	null	null	2
C	SQL	4	null	null	4

 

                    

                     

                      

                       

                      

                        

                 

               

                    

SELECT Name,max(列1) as 基础  FROM score GROUP BY Name;
SELECT Name,max(列2) as 爬虫  FROM score GROUP BY Name;
SELECT Name,max(列3) as SQL  FROM score GROUP BY Name;

 输出

A	1
B	2
C	3

 

             

          

  定义列1的思路

首先类比python语法if Name = "基础学科"：列1

推断sql的写法

 (case when stage = "基础学科"

           Then score

           else Null) as 列1

 把这个SQL列1放到max(列1)里