select * from 后有多个表的使用方法

 

 第一题：(1)

已知一个表的结构为：

-------------------

姓名 科目 成绩

张三 语文 20

张三 数学 30

张三 英语 50

李四 语文 70

李四 数学 60

李四 英语 90

 

怎样通过 select 语句把他变成以下结构：

------------------------------------

姓名 语文成绩 数学成绩 英语成绩

张三       20          30          50

李四       70          60          90

 

insert into student values(' 李四 ' , ' 英语 ' , '90' )

select * from student

-- --法一: 正 解如下：

select A. 姓名 , A. 成绩 as 语文成绩 , B. 成绩 as 数学成绩 , C. 成绩 as 英语成绩

from student A,student B,student C

where A. 姓名 = B. 姓名 and B. 姓名 = C. 姓名

and A. 科目 = ' 语文 ' and B. 科目 = ' 数学 ' and C. 科目 = ' 英语 '

 

-------- 理解如下：

select *

from student A,student B,student C

-- 将三个相同的 student 表相互连接，连接生成6 *6*6=216 条记录 , 因为每个表中有6条记录。

where A. 姓名 = B. 姓名 and B. 姓名 = C. 姓名

-- 对连接表记录进行筛选；得到 (3*3*3)+(3*3*3)=27+27=54 条记录。

and A. 科目 = ' 语文 ' and B. 科目 = ' 数学 ' and C. 科目 = ' 英语 '

-- 同时筛选三个子表中的科目内容 , 执行可得如下。

姓名    科目  成绩    姓名    科目   成绩    姓名    科目   成绩

张三    语文    20      张三    数学       30     张三    英语     50       

李四    语文    70      李四    数学       60     李四   英语     90       

再在select中定义一下输出即可。

---- 法二：正解如下

select姓名,

sum(case科目when '语文' then 成绩else 0 end) as 语文成绩,

sum(case科目when '数学' then 成绩else 0 end) as 数学成绩,

sum(case科目when '英语' then 成绩else 0 end) as 英语成绩,

avg(成绩) as 平均成绩,sum(成绩) as 总成绩

from student

group by 姓名

order by 姓名desc

结果如下:

姓名 语文成绩 数学成绩 英语成绩 平均成绩 总成绩

张三      20          30          50          33         100

李四      70          60          90          73          220

(2) 

create table A

(

year int,

Quarter varchar(30),

amount float

)

insert A select 2000,'1',1.1

insert A select 2000,'2',1.2

insert A select 2000,'3',1.3

insert A select 2000,'4',1.4

insert A select 2001,'1',2.1

insert A select 2001,'2',2.2

insert A select 2001,'3',2.3

insert A select 2001,'4',2.4

表 A 定义如下：
属性 类型
Year Integer
Quarter Varchar （ 30 ）
Amount float

Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
其中每行表表示一个季度的数据。

如果处理表 A 中的数据，得到如下的结果。
Year Quarter1 Quarter2 Quarter3 Quarter4
2000   1.1      1.2     1.3     1.4
2001   2.1      2.2     2.3     2.4
请用 SQL 写一段代码实现。

 

---法一: 正解如下：

select T1.YEAR,T1.amount as Quarter1,T2.amount as Quarter2,T3.amount as Quarter3,T4.amount as Quarter4

from A T1,A T2,A T3,A T4

WHERE T1.YEAR=T2.YEAR AND T2.YEAR=T3.YEAR AND T3.YEAR=T4.YEAR

AND T1.Quarter='1' and T2.Quarter='2' and T3.Quarter='3' and T4.Quarter='4'

---法二:正解如下：

select year,

sum(case Quarter when '1' then Amount else 0 end) as Quarter1,

sum(case Quarter when '2' then Amount else 0 end) as Quarter2,

sum(case Quarter when '3' then Amount else 0 end) as Quarter3,

sum(case Quarter when '4' then Amount else 0 end) as Quarter4,

sum(Amount) as ALLAmount

from A

group by year

order by year

 第二题：

有一张老师表 T(T_ID,T_NAME) ；

有一张学生表 S(S_ID,S_NAME) ；

有一张班级表 C(T_ID,S_ID,C_NAME) ，

其中 C_NAME 的取值只有 ‘ 大班 ’ 和 ‘ 小班 ’ ，

请查询出符合条件的老师的名字，条件是老师在大班中带的学生数大于此老师在小班中带的学生数。

（最好用子查询吧，题目是这么要求的，另数据库用的是 SQL Server ）

 

select *

from T,

( select count(*) as x,T_ID from C where c_name=' 小班 ' group by T_ID) a,

( select count(*) as x,T_ID from C where c_name=' 大班 ' group by T_ID) b

where b.x >a.x and a.T_ID=b.T_ID and T.T_ID=b.T_ID

 

第三题   某个公司的面试题，题目如下：

 

1 、找出哪些工资高于他们所在部门的平均工资的员工；

--------------------------------------------------

select A.*

from 工资表 a join （ select 部门代码， AVG （工资） as 平均工资 from 工资表 group by 部门代码） B

on a. 部门代码＝ B. 部门代码

where a. 工资 > B. 平均工资

 

 

2 、找出哪些工资高于他们所在部门的 manager( 经理）的工资的员工 ;

--------------------------------------------------------------

select A.*

from 工资表 a join （ select * from 工资表 where 职务＝经理） B

on a. 部门代码＝ B. 部门代码

where a. 工资 > B. 工资