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 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表
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 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...