今天面试SQL题目

Question

表test1 插入数据执行两次 要求用sql语句删除除自动编号外，其他相同的数据
 create table test1
(自动编号 int identity(1,1),
 学号 varchar(25) not null,
 科目 varchar(25) not null,
 成绩 float not null
)insert into test1
select '20070202','数学',82 UNION ALL
select '20070201','语文' ,85表test2 结构数据如下create table test2
(
  学号 int identity(1,1),
  科目 varchar(25) not null,
  成绩 float not null
)
insert into test2
select '数学',90 union all
select '数学',84 union all
select '数学',84 union all
select '数学',82 
要求写个存储过程执行结果如下
学号，科目，成绩，排名
1     数学   90    1
2     数学   84    2
3     数学   84    2
4     数学   82    4

Answer 1

1
DELETE TB  FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE . AND ID>T.ID)2SELECT *,(SELECT COUNT(*) FROM TB WHERE 成绩>=T.成绩)AS NUM FROM TB T

Answer 2

--1delete test1 from test1 t where 自动编号 not in (select min(自动编号) from test where 学号 = t.学号 and 科目 = t.科目 and 成绩 = t.成绩)

Answer 3

--2create table test2
(
  学号 int identity(1,1),
  科目 varchar(25) not null,
  成绩 float not null
)
insert into test2
select '数学',90 union all
select '数学',84 union all
select '数学',84 union all
select '数学',82 select *, 排名 = (select count(1) from test2 where 科目 = t.科目 and 成绩 > t.成绩) + 1  from test2 t order by 排名 , 学号drop table test2/*学号          科目                        成绩                                                    排名          
----------- ------------------------- ----------------------------------------------------- ----------- 
1           数学                        90.0                                                  1
2           数学                        84.0                                                  2
3           数学                        84.0                                                  2
4           数学                        82.0                                                  4（所影响的行数为 4 行）
*/

Answer 4

create table test2
(
  学号 int identity(1,1),
  科目 varchar(25) not null,
  成绩 float not null
)
insert into test2
select '数学',90 union all
select '数学',84 union all
select '数学',84 union all
select '数学',82 select *,排名=rank()over(order by 成绩) from test2
/*学号          科目                        成绩                     排名
----------- ------------------------- ---------------------- --------------------
4           数学                        82                     1
2           数学                        84                     2
3           数学                        84                     2
1           数学                        90                     4(4 行受影响)*/

Answer 5

create table test1
(自动编号 int identity(1,1),
 学号 varchar(25) not null,
 科目 varchar(25) not null,
 成绩 float not null
)insert into test1
select '20070202','数学',82 UNION ALL
select '20070201','语文' ,85 UNION ALL
select '20070202','数学',82 UNION ALL
select '20070201','语文' ,85SELECT *
FROM TEST1 T
WHERE NOT EXISTS(SELECT 1 FROM TEST1 WHERE T.学号 = 学号 AND T.科目 = 科目 AND T.成绩 = 成绩 AND T.自动编号>自动编号)DROP TABLE TEST1
--测试结果:
/*
自动编号        学号                        科目                        成绩                                                    
----------- ------------------------- ------------------------- ----------------------------------------------------- 
1           20070202                  数学                        82.0
2           20070201                  语文                        85.0（所影响的行数为 2 行）
*/create table test2
(
  学号 int identity(1,1),
  科目 varchar(25) not null,
  成绩 float not null
)
insert into test2
select '数学',90 union all
select '数学',84 union all
select '数学',84 union all
select '数学',82 SELECT 学号, 科目, 成绩, (SELECT COUNT(1)+1 FROM TEST2 WHERE T.成绩<成绩) AS 排名
FROM TEST2 T
DROP TABLE TEST2
--测试结果:
/*学号          科目                        成绩                                                    排名          
----------- ------------------------- ----------------------------------------------------- ----------- 
1           数学                        90.0                                                  1
2           数学                        84.0                                                  2
3           数学                        84.0                                                  2
4           数学                        82.0                                                  4（所影响的行数为 4 行）
*/

Answer 6

create table test1
(自动编号 int identity(1,1),
 学号 varchar(25) not null,
 科目 varchar(25) not null,
 成绩 float not null
)delete
  tb  
from 
  tb t 
where
  exists
(SELECT 1 FROM TB WHERE 学号=t.学号 and 科目=t.科目 and 自动编号<t.自动编号 )

Answer 7

应该是 slect *,排名=rank()over(order by 成绩 desc) from test2 
吧