oracle行转列

Case Statement：在Select语句，产生if/else的判断操作。

        

         Selectt.*,

              CASEusersex

                    WHEN '1' THEN

                     '男'

                    WHEN '0' THEN

                     '女'

                    ELSE

                     '未知'

                ENDusersex_str

           From T_userinfo t;

 

         适用于有大于，小于等情况

         Selectt.*,

              CASE

                    WHEN t.usersex= '1' THEN

                     '男'

                    WHEN t.usersex= '0' THEN

                     '女'

                    ELSE

                     '未知'

                ENDusersex_str

           From T_userinfo t;

 

         decode：oracle中专有函数：用于实现case的效果。

 

          select decode(usersex,'1', '男', '0', '女', '未知') as usersex, t.*

                   FromT_userinfo t;

 

                  

行转列

表字段为学生，学科，成绩

查询学生成绩时，我们需要将各个成绩转化为列

ROUND( number, decimal_places )

number : 需四舍五入处理的数值 
decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )

 

select t.*, t.rowid from CJ t;

 

Select *

 From (Select stu_name,

               sum(case

                     when stu_subject = '语文' then

                      round(stu_score, 2)

                     else

                      0

                   end) as 语文,

               sum(case

                     when stu_subject = '数学' then

                      round(stu_score, 2)

                     else

                      0

                   end) as 数学,

              

               sum(case

                     when stu_subject = '英语' then

                      round(stu_score, 2)

                     else

                      0

                   end) as 英语,

              sum(round(stu_score,2)) as 合计

         from cj

        group by stu_name

       union

       select '合计' as stu_name,

               (select sum(stu_score) from cjwhere stu_subject = '语文') as 语文,

               (select sum(stu_score) from cjwhere stu_subject = '数学') as 数学,

               (select sum(stu_score) from cjwhere stu_subject = '英语') as 英语,

               (select sum(stu_score) from cj )as 总成绩

         from dual) t

 order by t.合计 asc