高分求解

[herohua0509]

[liangCK]

作业真多。

[dawugui]

睡醒了再来做.

[geniuswjt]

作业自己解决吧

[dawugui]

1.2011-9-16 12:30:22 -- > 2011/09/16 12:30:22
1)sql server
--如果是个字符串
select replace(replace('2011-9-16 12:30:22','-','/'),':','')
/*
-----------------------
2011/9/16 123022

（所影响的行数为 1 行）

*/
--如果是个时间串
select convert(varchar(10),cast('2011-9-16 12:30:22' as datetime),111) + ' ' + convert(varchar(8),cast('2011-9-16 12:30:22' as datetime),108)
/*
------------------- 
2011/09/16 12:30:22

（所影响的行数为 1 行）
*/

2)oracle
--如果是个字符串
select replace(replace('2011-9-16 12:30:22','-','/'),':','')
/*
----------------
2011/9/16 123022

1 row selected.
*/

select replace(replace(to_char(to_date('2011-9-16 12:30:22', 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'),'-','/'),':','')  from dual;

/*
-----------------
2011/09/16 123022

1 row selected.
*/

[dawugui]

2.随机生成一百个人的成绩
1)sql server
select top 100 * from tb order by newid()

2)oracle
select t.* from tb t where rownum <= 100

3.统计出 100-85为优秀，85-60为及格，59-0为不及格的人数
--假设分数字段名为score
1)sql server
select (case when score >= 85 and score <= 100 then '优秀' 
            when score >= 60 and score <  85  then '及格'
            when score >= 0  and score <  60  then '不及格'
       end) 分数情况 , count(1) 人数
from tb
group by 
(case when score >= 85 and score <= 100 then '优秀' 
      when score >= 60 and score <  85  then '及格'
      when score >= 0  and score <  60  then '不及格'
end)

2)oracle
select (case when score >= 85 and score <= 100 then '优秀' 
            when score >= 60 and score <  85  then '及格'
            when score >= 0  and score <  60  then '不及格'
       end) 分数情况 , count(1) 人数
from tb
group by 
(case when score >= 85 and score <= 100 then '优秀' 
      when score >= 60 and score <  85  then '及格'
      when score >= 0  and score <  60  then '不及格'
end)
  

[--小F--]

---查询10-15名的
;with f as
(
select px=row_number()over(order by score) from table1
)

select * from f where px between 10 and 15

[--小F--]

select 
  sname,avg(score) 
from
   table1
group by
   sname

[dawugui]

4.按组号分组汇总出,每组的数据条数
1) sql server
select groupid , count(*) cnt from tb group by groupid
select groupid , count(1) cnt from tb group by groupid
2) oracle
select groupid , count(*) cnt from tb group by groupid
select groupid , count(1) cnt from tb group by groupid

5.userinfo表中有12条数据,每4条相同,要求删除重复的数据,最后剩余3条.
1)sql server
select distinct * into tmp from tb
truncate table tb
insert into tb select * from tmp
drop table tmp

2)oracle
create table tmp from select distinct * from tb
truncate table tb
insert into tb select * from tmp
drop table tmp

6.最后那个表格题
A.查询分数在10-15名的.只按照score来查,不分课程.
1)sql server
select top 6 * from (select top 15 * from table1 order by score desc) order by score

2)oracle
select * (除了px字段不要为) from
(
  select t.* , row_number() over(order by score desc) px from table1
) m

B.用一条语句查询出每门课程都大于85的学生姓名
1)sql server
select * from table1 where sno not in (select distinct sno from table1 where score <= 85)
2)oracle
select t.* from table1 t where sno not in (select distinct sno from table1 where score <= 85)

C.用sql语句得到平均分.
1)sql server
select sno , sname , cast(avg(score*1.0) as decimal(18,2)) 平均分 from table1 group by sno , sname

2)oracle
select sno , sname , trunc(avg(score),2) 平均分 from table1 group by sno , sname

7.转化字符串'2008012015:12:34' --> '2008-01-20 15:12:34'
1)sql server
select substring('2008012015:12:34',1,4) + '-' + substring('2008012015:12:34',5,2) + '-' +substring('2008012015:12:34',7,2) + ' ' + substring('2008012015:12:34',9,8)
/*
                                    
----------------------------------- 
2008-01-20 15:12:34

（所影响的行数为 1 行）
*/

2.oracle
select substr('2008012015:12:34',1,4) || '-' || substr('2008012015:12:34',5,2) || '-' || substr('2008012015:12:34',7,2) || ' ' || substr('2008012015:12:34',9,8) from dual;

/*
-------------------
2008-01-20 15:12:34

1 row selected.
*/

[dawugui]

5.userinfo表中有12条数据,每4条相同,要求删除重复的数据,最后剩余3条.
1)sql server
select distinct * into tmp from tb
truncate table tb
insert into tb select * from tmp
drop table tmp

2)oracle
create table tmp from select distinct * from tb
truncate table tb
insert into tb select * from tmp
drop table tmp

--修正上面oracle部分

2)oracle
create table tmp as from select distinct * from tb
truncate table tb
insert into tb select * from tmp
drop table tmp

[qq451763407]

关注下，