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