|
锁定老帖子 主题：2道sql面试题
精华帖 (0) :: 良好帖 (0) :: 隐藏帖 (0)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2011-07-12
行转列问题
|
| 返回顶楼 | |
|
发表时间：2011-07-12
dolwenjian 写道 zhouYunan2010 写道 streamwalk 写道 第1题
select a.class, sum(decode(a.sex, 'M', 1, 0)) as malecount, sum(decode(a.sex, 'F', 1, 0)) as femalecount from schools a group by a.class having sum(decode(a.sex, 'M', 1, 0)) = sum(decode(a.sex, 'F', 1, 0)) 很不错了。 
不过有一个bug，在sex上添加check约束时decode函数将失效。 check约束时 会失效。 这个能详细说下么，貌似还没碰到过。。 就是给字段sex加check约束时malecount或femalecount获取的结果都是decode()的default值0. |
| 返回顶楼 | |
|
发表时间：2011-07-12
--第二题
select 姓名,
sum(decode(科目,'语文',成绩,null)) "语文",
sum(decode(科目,'数学',成绩,null)) "数学",
sum(decode(科目,'英语',成绩,null)) "英语"
from grade
group by 姓名
也可以使用PL/SQL语句实现 |
| 返回顶楼 | |
|
发表时间：2011-07-12
第一道，我是这样的:
select distinct class from schools where class in(select cl from (select class cl, count(name) ct from schools group by cl, sex) tmp group by cl,ct having count(*)>1) |
| 返回顶楼 | |
|
发表时间：2011-07-12
LinJiuPing 写道 第一道，我是这样的:
select distinct class from schools where class in(select cl from (select class cl, count(name) ct from schools group by class, sex) tmp group by cl,ct having count(*)>1) 算法不错。不过红色处要改一下。 |
| 返回顶楼 | |
|
发表时间：2011-07-13
zhouYunan2010 写道 streamwalk 写道 第1题
select a.class, sum(decode(a.sex, 'M', 1, 0)) as malecount, sum(decode(a.sex, 'F', 1, 0)) as femalecount from schools a group by a.class having sum(decode(a.sex, 'M', 1, 0)) = sum(decode(a.sex, 'F', 1, 0)) 很不错了。
不过有一个bug，在sex上添加check约束时decode函数将失效。 看来兄弟是SQL高手哈，有空向你学习学习 
|
| 返回顶楼 | |
|
发表时间：2011-07-13
zhouYunan2010 写道 zhanghh321 写道 select tem.c, sum(tem.f) total
from (select class c, sex s, count(*) f from school group by class and sex) tem group by tem.c having total = tem.f/2 第一题这么做可以吗 很遗憾，你错得很宽广。而且这句亮了having total = tem.f/2 having 怎么了？ 明说嘛 |
| 返回顶楼 | |
|
发表时间：2011-07-13
第二题： select name as 科目, sum(decode(subject,'语文',score,0)) as 语文, sum(decode(subject,'数学',score,0)) as 数学, sum(decode(subject,'英语',score,0)) as 英语, from grade group by name;
|
| 返回顶楼 | |
|
发表时间：2011-07-13
zhanghh321 写道 zhouYunan2010 写道 zhanghh321 写道 select tem.c, sum(tem.f) total
from (select class c, sex s, count(*) f from school group by class and sex) tem group by tem.c having total = tem.f/2 第一题这么做可以吗 很遗憾，你错得很宽广。而且这句亮了having total = tem.f/2 having 怎么了？ 明说嘛 额，，谢谢啦 我改过来了 确实访问不到那个列，已经修改过来了，当时写的时候没有测。 select tem.c, sum(tem.f) total, tem.f sexAmount from (select class c, sex s, count(*) f from school group by class,sex) tem group by tem.c having total = sexAmount*2 |
| 返回顶楼 | |
|
发表时间：2011-07-13
最后修改：2011-07-13
select class ,
sum(case when(sex='M') then 1 else 0 end) m ,
sum(case when(sex='F') then 1 else 0 end) fel from schools
group by class
having
sum(case when(sex='M') then 1 else 0 end) =
sum(case when(sex='F') then 1 else 0 end)
select
name,
sum(case when(project='语文') then mark else null end) 语文,
sum(case when(project='数学') then mark else null end) 数学,
sum(case when(project='英语') then mark else null end) 英语
from
grade
group by name;
|
| 返回顶楼 | |
