1.在面试过程中多次碰到两道SQL查询的题目,一是查询A(ID,Name)表中第31至40条记录,ID作为主键可能是不是连续增长的列。
select id,name from
(select id, name,rownum r from A) t
where t.r>30 and t.r<41;
升级版:将表A(id,name)按id升序排列,并取出第6至10行的记录。
select id ,name from
(
select id,name,rownum r from
(
select id,name from A order by asc
)
)where r>5 and r<11;
2.SQL语句面试题,表内容如下:
2005-05-09 胜2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果,该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
解:
create table t (
t_date verchar2(10),
score verchar2(2),
);
insert into t values ('2005-05-09','胜');insert into t values ('2005-05-09','胜');
insert into t values ('2005-05-09','负');insert into t values ('2005-05-09','负');
insert into t values ('2005-05-10','胜');
insert into t values ('2005-05-10','负');insert into t values ('2005-05-10','负');
select t_date, 胜,负 from
(select t_date,count(*) 胜 from t where score='胜' group by t_date) t1
join (select t_date,count(*) 负 from t where score='负' group by t_date) t2
on(t1.t_date=t2.t_date);
语句面试题,关于
group by
表内容:
2005-05-09
胜
2005-05-09
胜
2005-05-09
负
2005-05-09
负
2005-05-10
胜
2005-05-10
负
2005-05-10
负
如果要生成下列结果
,
该如何写
sql
语句
?
胜
负
2005-05-09 2 2
2005-05-10 1 2