加载中…select * from 后有多个表的使用方法[转]
(2012-03-27 13:55:34)
标签：
杂谈 |
分类： Oracle/SQL |
已知一个表的结构为：
-------------------
姓名 科目 成绩
张三
语文
张三
数学
张三
英语
李四
语文
李四
数学
李四
英语
怎样通过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写一段代码实现。
属性类型
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
2001
---法一:正解如下：
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.工资
喜欢
0
赠金笔