SQL 连接(JOIN)，行转列，列转行

curtime

已于 2022-12-13 08:20:16 修改

阅读量450

点赞数

文章标签： sql 数据库

于 2022-12-07 23:49:05 首次发布

本文链接：https://blog.csdn.net/m0_74162514/article/details/128229085

版权

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

使用case.. when... then 进行行转列

mysql> select * from sc;
+------+------+-------+
| SID  | CID  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 01   | 02   |  90.0 |
| 01   | 03   |  99.0 |
| 02   | 01   |  70.0 |
| 02   | 02   |  60.0 |
| 02   | 03   |  80.0 |
| 03   | 01   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 03   |  80.0 |
| 04   | 01   |  50.0 |
| 04   | 02   |  30.0 |
| 04   | 03   |  20.0 |
| 05   | 01   |  76.0 |
| 05   | 02   |  87.0 |
| 06   | 01   |  31.0 |
| 06   | 03   |  34.0 |
| 07   | 02   |  89.0 |
| 07   | 03   |  98.0 |
+------+------+-------+
18 rows in set (0.00 sec)

mysql> select * from student;
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-03-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-07-01 00:00:00 | 女   |
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |
+------+--------+---------------------+------+
8 rows in set (0.00 sec)


mysql> select stu.sname,
sum(case when s.cid=01 then s.score else 0 end )语文,
sum(case when s.cid=02 then s.score else 0 end )数学, 
sum(case when s.cid=03 then s.score else 0 end )英语   
from sc s 
join student stu on s.sid=stu.sid 
group by stu.sname;
+--------+--------+--------+--------+
| sname  | 语文   | 数学   | 英语   |
+--------+--------+--------+--------+
| 赵雷   |   80.0 |   90.0 |   99.0 |
| 钱电   |   70.0 |   60.0 |   80.0 |
| 孙风   |   80.0 |   80.0 |   80.0 |
| 李云   |   50.0 |   30.0 |   20.0 |
| 周梅   |   76.0 |   87.0 |    0.0 |
| 吴兰   |   31.0 |    0.0 |   34.0 |
| 郑竹   |    0.0 |   89.0 |   98.0 |
+--------+--------+--------+--------+
7 rows in set (0.01 sec)

列转行

mysql> select sid,
concat_ws('  ',sid ,cid,score)info  
from sc 
limit 3;
+------+--------------+
| sid  | info         |
+------+--------------+
| 01   | 01  01  80.0 |
| 01   | 01  02  90.0 |
| 01   | 01  03  99.0 |
+------+--------------+
3 rows in set (0.00 sec)