1.横纵表转换
A表
|
Name |
Course |
Grade |
|
Alex |
English |
80 |
|
Alex |
Chinese |
70 |
|
Alex |
Japanese |
85 |
|
Bob |
English |
75 |
|
Bob |
Chinese |
85 |
|
Bob |
Japanese |
80 |
B表
|
Name |
English |
Chinese |
Japanese |
|
Alex |
80 |
70 |
85 |
|
Bob |
75 |
85 |
80 |
|
Chris |
90 |
70 |
60 |
A -> B
select Name,
SUM(case Course when 'English' then Grade else end) as English,
SUM(case Course when 'Chinese' then Grade else end) as Chinese,
SUM(case Course when 'Japanese' then Grade else end) as Japanese
from TranscriptA
group by Name
B -> A
select Name,'English' AS Course,English AS Grade from TranscriptB
union all
select Name,'Chinese' AS Course,Chinese AS Grade from TranscriptB
union all
select Name,'Japanese' AS Course,Japanese AS Grade from TranscriptB
order by Name,Course desc;
2.假设有学习成绩表
用一条SQL查出每一门成绩都大于80的学生的姓名
select distinct name from table where name not in (select distinct name from table where chengji <= ) select name from table group by name having min(chengji)>
3.假设有Team表，只有一个Name字段，代表球队名字
用SQL查处各队之间互相比赛的组合
select a.name, b.name from team a, team b where a.name< b.name
4.统计工资累加
A表
|
Year |
Salary |
| 2010 |
1000 |
|
2011 |
1500 |
| 2012 |
2000 |
| 2013 |
2500 |
B表
|
Year |
Salary |
| 2010 |
1000 |
|
2011 |
2500 |
| 2012 |
4500 |
| 2013 |
7000 |
A -> B
select b.Year, sum(a.Salary) AccSalary from TA a, TA b
where a.Year <= b.Year
group by b.Year select a.year, (select sum(b.Salary) from TA b
where b.Year <= a.year) AccSalary
from TA a
To be continue...