星空网 > 软件开发 > 数据库

面试题整理:SQL(一)

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




原标题:面试题整理:SQL(一)

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流