SQL实现行转列和列转行简单例子

supuerlovepc

已于 2024-01-04 16:37:34 修改

阅读量393

点赞数 8

文章标签： sql 数据库

于 2024-01-04 16:21:42 首次发布

本文链接：https://blog.csdn.net/supuerlovepc/article/details/135389861

版权

一、行转列

-- oracle创建表
create table score
(
    id  NUMBER(8) not null,
    subject    VARCHAR2(20),
    score NUMBER(10)
);
原表结构：
| ID | SUBJECT | SCORE |
| 1 | 数学 | 100 |
| 2 | 数学 | 60 |
| 1 | 语文 | 100 |
| 2 | 语文 | 60 |


-- 1.经典的sum(max也可以)+if(case when也可以)+group by
select id,
       sum(case subject
           when 'math' then score
        else null end) math,
       sum(case subject
           when 'chinese' then score
           else null end) chinese
from score group by id;

-- 2.连接
select a.id, a.score math, b.score chinese
from (
         select * from score where subject = 'math') a
         left join (select * from score where subject = 'chinese') b
                   on a.id = b.id;
                   
-- 3.使用natural join 两表使用自然连接时（如table1:id,math ~ table2:id,chinese => table3:id,math,chinese）
-- 会根据同名列连接，如同名例字段值也相同，会合并程一行。如果没有同名列，则进行笛卡尔积。
select *
from (
         select id, score math from score where subject = 'math') a
         natural join (select id, score chinese from score where subject = 'chinese') b;

-- 4.union all + 分组


结果：
| ID | MATH | CHINESE |
| 1 | 100 | 100 |
| 2 | 60 | 60 |

二、列转行

原表结构：
| ID | MATH | CHINESE |
| 1 | 100 | 100 |
| 2 | 60 | 60 |

-- 列转行(把上面的行转列结果直接拿过来，用列转行复原)
-- 1. union all
select id, '数学' subject, math score
from (select *
      from (
               select id, score math from score where subject = 'math') a
               natural join (select id, score chinese from score where subject = 'chinese') b)
union all
select id, '语文' subject, chinese score
from (select *
      from (
               select id, score math from score where subject = 'math') a
               natural join (select id, score chinese from score where subject = 'chinese') b)
;

结果：
| ID | SUBJECT | SCORE |
| 1 | 数学 | 100 |
| 2 | 数学 | 60 |
| 1 | 语文 | 100 |
| 2 | 语文 | 60 |