id name subject score createdate1.创建时间为3天前0点创建的纪录,20分钟前创建的纪录?
2.3门以上不及格学生的学生姓名?
3. id name
1 a
2 b
3 a
4 a
id为identity,只留一条a与一条b
4.总分排名5-7的学生姓名(name,score),最好写成存储过程,请注意并列排名的问题
2.3门以上不及格学生的学生姓名?
3. id name
1 a
2 b
3 a
4 a
id为identity,只留一条a与一条b
4.总分排名5-7的学生姓名(name,score),最好写成存储过程,请注意并列排名的问题
解决方案 »
- 求救!SQL2008没有智能提示,是咋回事啊!
- 高手帮个忙,都搞混了,在线等!
- 如何在sql server 2005解决 登陆失败问题?
- 如何查询两个表的数据总量
- 有关存储过程的应用
- ===请教,如果写存储过程,数据库里想备份最近7天的数据,帮帮小第!===
- VS2005里有个Microsoft SQL Server 2005能用嘛?
- SCOPE_IDENTITY(),@@IDENTITY在哪些情况下返回NULL啊?
- 请问我想把当前时间减掉一个固定时间的sql语句该怎么写?
- +++++++++学习数据库,就一定要学好SQL吗?请大虾推荐一二本SQL的好书。++++++++++++
- sql server里update时,是行锁还是表锁
- 急,怎样在SQL SERVER 2000中调用Oracle存储过程
select dateadd(mi,-20,getdate())select name
from table1 where id in (
select id
from table1
where score<60
group by id
having Count(*)>3)a
delete * from table1 where exists(select id from table1 b where table1.name=b.name and table1.id>b.id )
更正为:
大家可以参考~!
select * from table1 where ID in (
select max(ID) as ID from table1 group by name)
1 a
2 b
3 a
4 a
id为identity,只留一条a与一条b直接 select top 2 id name from t不就可以了
这里取总分排名2-3名,已考虑并列排名情况declare @tb Table(id int,name varchar(20),subject varchar(20),score tinyint,createdate Datetime)
insert into @tb
select 1,'麦当娜','数学',56,getdate() union all
select 1,'麦当娜','物理',56,getdate() union all
select 1,'麦当娜','化学',56,getdate() union all
select 1,'麦当娜','政治学',46,getdate() union all
select 1,'史泰龙','数学',86,getdate() union all
select 1,'史泰龙','物理',76,getdate() union all
select 1,'史泰龙','化学',56,getdate() union all
select 1,'史泰龙','政治学',36,getdate() union all
select 1,'施瓦辛格','数学',46,getdate() union all
select 1,'施瓦辛格','物理',76,getdate() union all
select 1,'施瓦辛格','化学',56,getdate() union all
select 1,'施瓦辛格','政治学',36,getdate() union all
select 1,'迈克.杰克孙','数学',46,getdate() union all
select 1,'迈克.杰克孙','物理',76,getdate() union all
select 1,'迈克.杰克孙','化学',96,getdate() union all
select 1,'迈克.杰克孙','政治学',36,getdate() union all
select 1,'奥巴马','数学',46,getdate() union all
select 1,'奥巴马','物理',76,getdate() union all
select 1,'奥巴马','化学',96,getdate() union all
select 1,'奥巴马','政治学',76,getdate()
select * from
(select name,Sum(Score)as TotalScore from @tb group by name) j where j.TotalScore in
(
select TotalScore from
(
select *,Row_Number() over(order by TotalScore desc)as rn from
(
select max(name) as Name,TotalScore from
(select name,Sum(Score)as TotalScore from @tb group by name) t
group by t.TotalScore) s) k
where k.rn>=2 and k.rn<=3) order by j.TotalScore desc
(20 行受影响)
name TotalScore
-------------------- -----------
迈克.杰克孙 254
史泰龙 254
麦当娜 214
施瓦辛格 214(4 行受影响)
drop table a
go
create Table a(id int,name varchar(20),subject varchar(20),score tinyint,createdate Datetime)
insert into a
select 1,'麦当娜','数学',56,getdate() union all
select 1,'麦当娜','物理',56,getdate() union all
select 1,'麦当娜','化学',56,getdate() union all
select 1,'麦当娜','政治学',46,getdate() union all
select 1,'史泰龙','数学',86,getdate() union all
select 1,'史泰龙','物理',76,getdate() union all
select 1,'史泰龙','化学',56,getdate() union all
select 1,'史泰龙','政治学',36,getdate() union all
select 1,'施瓦辛格','数学',46,getdate() union all
select 1,'施瓦辛格','物理',76,getdate() union all
select 1,'施瓦辛格','化学',56,getdate() union all
select 1,'施瓦辛格','政治学',36,getdate() union all
select 1,'迈克.杰克孙','数学',46,getdate() union all
select 1,'迈克.杰克孙','物理',76,getdate() union all
select 1,'迈克.杰克孙','化学',96,getdate() union all
select 1,'迈克.杰克孙','政治学',36,getdate() union all
select 1,'奥巴马','数学',46,getdate() union all
select 1,'奥巴马','物理',76,getdate() union all
select 1,'奥巴马','化学',96,getdate() union all
select 1,'奥巴马','政治学',76,getdate()
go
select * from
(
select dense_rank() over(order by result desc) as id , name , result
from
(
select name , sum(score) as result from a group by name
)b
)c
where id in (2,3)
--------------------------------
id name result
-------------------- -------------------- -----------
2 迈克.杰克孙 254
2 史泰龙 254
3 麦当娜 214
3 施瓦辛格 214
drop table a
go
create Table a(id int,name varchar(20),subject varchar(20),score tinyint,createdate Datetime)
insert into a
select 1,'麦当娜','数学',56,getdate() union all
select 1,'麦当娜','物理',56,getdate() union all
select 1,'麦当娜','化学',56,getdate() union all
select 1,'麦当娜','政治学',46,getdate() union all
select 1,'史泰龙','数学',86,getdate() union all
select 1,'史泰龙','物理',76,getdate() union all
select 1,'史泰龙','化学',56,getdate() union all
select 1,'史泰龙','政治学',36,getdate() union all
select 1,'施瓦辛格','数学',46,getdate() union all
select 1,'施瓦辛格','物理',76,getdate() union all
select 1,'施瓦辛格','化学',56,getdate() union all
select 1,'施瓦辛格','政治学',36,getdate() union all
select 1,'迈克.杰克孙','数学',46,getdate() union all
select 1,'迈克.杰克孙','物理',76,getdate() union all
select 1,'迈克.杰克孙','化学',96,getdate() union all
select 1,'迈克.杰克孙','政治学',36,getdate() union all
select 1,'奥巴马','数学',46,getdate() union all
select 1,'奥巴马','物理',76,getdate() union all
select 1,'奥巴马','化学',96,getdate() union all
select 1,'奥巴马','政治学',76,getdate()
go
select * from
(
select dense_rank() over(order by result desc) as id , name , result
from
(
select name , sum(score) as result from a group by name
)b
)c
where id in (2,3)
--------------------------------
id name result
-------------------- -------------------- -----------
2 迈克.杰克孙 254
2 史泰龙 254
3 麦当娜 214
3 施瓦辛格 214
insert into @tb
select 1,'麦当娜','数学',56,getdate() union all
select 1,'麦当娜','物理',56,getdate() union all
select 1,'麦当娜','化学',56,getdate() union all
select 1,'麦当娜','政治学',46,getdate() union all
select 1,'史泰龙','数学',86,getdate() union all
select 1,'史泰龙','物理',76,getdate() union all
select 1,'史泰龙','化学',56,getdate() union all
select 1,'史泰龙','政治学',36,getdate() union all
select 1,'施瓦辛格','数学',46,getdate() union all
select 1,'施瓦辛格','物理',76,getdate() union all
select 1,'施瓦辛格','化学',56,getdate() union all
select 1,'施瓦辛格','政治学',36,getdate() union all
select 1,'迈克.杰克孙','数学',46,getdate() union all
select 1,'迈克.杰克孙','物理',76,getdate() union all
select 1,'迈克.杰克孙','化学',96,getdate() union all
select 1,'迈克.杰克孙','政治学',36,getdate() union all
select 1,'奥巴马','数学',46,getdate() union all
select 1,'奥巴马','物理',76,getdate() union all
select 1,'奥巴马','化学',96,getdate() union all
select 1,'奥巴马','政治学',76,getdate()select top 3 * from
(select top 7 name,sum(score) as TotalScore from @tb group by name order by name asc)
tt order by name desc
--select * from student where datediff(day,createdate,getdate())=3 and
--datepart(hh,createdate)=0
--
--select * from student where datediff(mi,createdate,getdate())>61
--
--
--select * from student where name='bb'
--
--select name from student
--where score<60
--group by name
--having count(subject)>2
--
--select Max(id),name from student
--group by name
select name,sumscore from(
select sum(score) as sumscore,name,ROW_NUMBER() over (order by sum(score) desc) as num from student
group by name
) as a
where num >1 and num <3