面试题整理：SQL（一）

1.横纵表转换

A表

B表

A -> B

select Name, SUM(case Course when 'English' then Grade else 0 end) as English,SUM(case Course when 'Chinese' then Grade else 0 end) as Chinese,SUM(case Course when 'Japanese' then Grade else 0 end) as Japanesefrom TranscriptAgroup by Name

B -> A

select Name,'English' AS Course,English  AS Grade from TranscriptBunion all select Name,'Chinese' AS Course,Chinese AS Grade from TranscriptB union all select Name,'Japanese' AS Course,Japanese AS Grade from TranscriptBorder by Name,Course desc;

2.假设有学习成绩表

用一条SQL查出每一门成绩都大于80的学生的姓名

select distinct name from table where name not in (select distinct name from table where chengji <= 80)select name from table group by name having min(chengji)>80

3.假设有Team表，只有一个Name字段，代表球队名字

用SQL查处各队之间互相比赛的组合

select a.name, b.name from team a, team b where a.name< b.name

4.统计工资累加

A表

B表

A -> B

select b.Year, sum(a.Salary) AccSalary from TA a, TA bwhere a.Year <= b.Yeargroup by b.Yearselect a.year, (select sum(b.Salary) from TA b where b.Year <= a.year) AccSalary from TA a

To be continue...