一个简单的SQL语句练习,也是一个比较经典的横表转纵表练习
效果如下:
查询以后达到如下效果;
sql语句如下:
![ContractedBlock.gif](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1
create
table
subject(
2 sid int identity ( 1 , 1 ),
3 sno int ,
4 subject varchar ( 10 ),
5 score int
6 )
7
8 insert into subject values ( 1 , ' 语文 ' , 90 )
9 insert into subject values ( 1 , ' 数学 ' , 70 )
10 insert into subject values ( 1 , ' 英语 ' , 80 )
11 insert into subject values ( 2 , ' 语文 ' , 80 )
12 insert into subject values ( 2 , ' 数学 ' , 90 )
13 insert into subject values ( 2 , ' 英语 ' , 70 )
14
15 select * from subject
16
17 select DISTINCT s1.sno as ' 学号 ' ,s1.score as ' 语文 ' ,s3.score as ' 英语 ' ,s2.score as ' 数学 '
18 from subject as s1,subject as s2,subject as s3
19 where s1.sno = s2.sno and s2.sno = s3.sno
20 and s1.subject = ' 语文 ' and s2.subject = ' 数学 ' and s3.subject = ' 英语 '
21
22
2 sid int identity ( 1 , 1 ),
3 sno int ,
4 subject varchar ( 10 ),
5 score int
6 )
7
8 insert into subject values ( 1 , ' 语文 ' , 90 )
9 insert into subject values ( 1 , ' 数学 ' , 70 )
10 insert into subject values ( 1 , ' 英语 ' , 80 )
11 insert into subject values ( 2 , ' 语文 ' , 80 )
12 insert into subject values ( 2 , ' 数学 ' , 90 )
13 insert into subject values ( 2 , ' 英语 ' , 70 )
14
15 select * from subject
16
17 select DISTINCT s1.sno as ' 学号 ' ,s1.score as ' 语文 ' ,s3.score as ' 英语 ' ,s2.score as ' 数学 '
18 from subject as s1,subject as s2,subject as s3
19 where s1.sno = s2.sno and s2.sno = s3.sno
20 and s1.subject = ' 语文 ' and s2.subject = ' 数学 ' and s3.subject = ' 英语 '
21
22