Select 多个表并且相关联转置,

佚名 6年前 (2019-04-12) 随笔 829人围观抢沙发百度已收录

已知一个表的结构为： ------------------- 姓名科目成绩张三语文 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

第二题：

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。有一张老师表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.工资,已知一个表的结构为： ------------------- 姓名科目成绩张三语文 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)

第二题：

有一张老师表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.工资