前天做的一个数据库的笔试

有表一查询出表二的样式

 

（表一）

 

（表二）

 

占座。、。。

select t.stname,t.english as score,'英语' as subjects from usertables t
UNION
select m.stname, m.maths as score,'数学' as subjects from usertables m

查询出来的顺序换一下

又是列转行的问题, 看来考官还真是喜欢考啊。


准备数据：

====================================

create table course (stname varchar(10), math int, english int);
insert into course ('Jame', 65, 97), ('Tom',88,59),('calvin',98,99);

//pivot是工具表，列转行一定会用到
create table pivot (id int);
insert into pivot values (1),(2);

实现：
==========================================

select stname,

case id
when 1 then 'Math'
when 2 then 'English'
else 0
end as subject,


case id
when 1 then math
when 2 then english
else 0
end as grade

from course,pivot

SQL> create table st
  2  (
  3  stname varchar2(20),
  4  math integer,
  5  english integer
  6  );

Table created

SQL>
SQL> insert into st values('张三', 80, 97);

1 row inserted
SQL> insert into st values('李四', 76, 86);

1 row inserted
SQL> commit;

Commit complete

SQL>
SQL> select stname as name, '数学' as subject, math as grade from st s
  2  union all
  3  select stname as name, '英语' as subject, english as grade from st t
  4  order by name;

NAME                 SUBJECT       GRADE
-------------------- ------- ----------
张三                 英语            97
张三                 数学            80
李四                 英语            86
李四                 数学            76

SQL>

select st.stname as name,
case subject
when 1 then '数学'
when 2 then '英语'
end as subject,
case subject
when 1 then st.math
when 2 then st.english
end as grade
from st, (select level subject from dual connect by level <= 2) tmp;

很好，我学习一下转行