34,591
社区成员
发帖
与我相关
我的任务
分享
/**
ScoreId
-----------
3
4
8
9
(所影响的行数为 4 行)
**/
select ScoreId from Score
where
ScoreId in ( select top 2 ScoreId from Score where ScoreType='考核1' and ScoreUser='张三' order by ScoreInfo desc)
or
Scoreid in (select top 2 ScoreId from Score where ScoreType='考核1' and ScoreUser='张三' order by ScoreInfo)
select ScoreId from Score
where
(
ScoreId in(select top 2 ScoreId from Score where ScoreType='考核1' and ScoreUser='张三' order by ScoreInfo desc)
or
Scoreid in(select top 2 ScoreId from Score where ScoreType='考核1' and ScoreUser='张三' order by ScoreInfo)
)
and ScoreType='考核1' and ScoreUser='张三'
create table tb(ScoreId int, fen int, ScoreType varchar(10), ScoreUser varchar(10))
insert into tb values(3 , 100, '考核1' , '张三')
insert into tb values(4 , 99 , '考核1' , '张三')
insert into tb values(5 , 97 , '考核1' , '张三')
insert into tb values(6 , 96 , '考核1' , '张三')
insert into tb values(7 , 99 , '考核1' , '李四')
insert into tb values(8 , 95 , '考核1' , '张三')
insert into tb values(9 , 94 , '考核1' , '张三')
insert into tb values(10, 99 , '考核2' , '张三')
insert into tb values(11, 99 , '考核2' , '李四')
go
-- 姓名为张三考核类型为考核1的2个最低记录
select top 2 * from tb where ScoreUser = '张三' and ScoreType = '考核1' order by fen
/*
ScoreId fen ScoreType ScoreUser
----------- ----------- ---------- ----------
9 94 考核1 张三
8 95 考核1 张三
(所影响的行数为 2 行)
*/
-- 姓名为张三考核类型为考核1的2个最高记录
select top 2 * from tb where ScoreUser = '张三' and ScoreType = '考核1' order by fen desc
/*
ScoreId fen ScoreType ScoreUser
----------- ----------- ---------- ----------
3 100 考核1 张三
4 99 考核1 张三
(所影响的行数为 2 行)
*/
--合在一起显示
select top 100 percent * from (select top 2 * from tb where ScoreUser = '张三' and ScoreType = '考核1' order by fen) t
union all
select top 100 percent * from (select top 2 * from tb where ScoreUser = '张三' and ScoreType = '考核1' order by fen desc) t
order by scoreid
/*
ScoreId fen ScoreType ScoreUser
----------- ----------- ---------- ----------
3 100 考核1 张三
4 99 考核1 张三
8 95 考核1 张三
9 94 考核1 张三
(所影响的行数为 4 行)
*/
drop table tb