34,590
社区成员
发帖
与我相关
我的任务
分享
--同分时,按scoreid来取.只取两条
create table tb(ScoreId int, ScoreInfo 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 ScoreInfo , ScoreId
/*
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 ScoreInfo desc , ScoreId 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 ScoreInfo , ScoreId) t
union all
select top 100 percent * from (select top 2 * from tb where ScoreUser = '张三' and ScoreType = '考核1' order by ScoreInfo desc , ScoreId 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
--同分时,把同分的都取出来,有多少取多少.
create table tb(ScoreId int, ScoreInfo 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 t.* from tb t where ScoreUser = '张三' and ScoreType = '考核1' and ScoreInfo in (select top 2 ScoreInfo from tb where ScoreUser = '张三' and ScoreType = '考核1' order by ScoreInfo , ScoreId)
/*
ScoreId fen ScoreType ScoreUser
----------- ----------- ---------- ----------
9 94 考核1 张三
8 95 考核1 张三
(所影响的行数为 2 行)
*/
-- 姓名为张三考核类型为考核1的2个最高记录
select t.* from tb t where ScoreUser = '张三' and ScoreType = '考核1' and ScoreInfo in (select top 2 ScoreInfo from tb where ScoreUser = '张三' and ScoreType = '考核1' order by ScoreInfo desc, ScoreId desc)
/*
ScoreId fen ScoreType ScoreUser
----------- ----------- ---------- ----------
3 100 考核1 张三
4 99 考核1 张三
(所影响的行数为 2 行)
*/
--合在一起显示
select top 100 percent * from (select t.* from tb t where ScoreUser = '张三' and ScoreType = '考核1' and ScoreInfo in (select top 2 ScoreInfo from tb where ScoreUser = '张三' and ScoreType = '考核1' order by ScoreInfo )) m
union all
select top 100 percent * from (select t.* from tb t where ScoreUser = '张三' and ScoreType = '考核1' and ScoreInfo in (select top 2 ScoreInfo from tb where ScoreUser = '张三' and ScoreType = '考核1' order by ScoreInfo desc)) m
order by scoreid , ScoreInfo
/*
ScoreId fen ScoreType ScoreUser
----------- ----------- ---------- ----------
3 100 考核1 张三
4 99 考核1 张三
8 95 考核1 张三
9 94 考核1 张三
(所影响的行数为 4 行)
*/
drop table tb
---测试数据---
if object_id('[Score]') is not null drop table [Score]
go
create table [Score]([ScoreId] int,[ScoreInfo] int,[ScoreType] varchar(5),[ScoreUser] varchar(4))
insert [Score]
select 3,99,'考核1','张三' union all
select 4,99,'考核1','张三' union all
select 5,99,'考核1','张三' union all
select 6,99,'考核1','张三' union all
select 7,99,'考核1','李四' union all
select 8,99,'考核1','张三' union all
select 9,99,'考核1','张三' union all
select 10,99,'考核2','张三' union all
select 11,99,'考核2','李四'
---查询---
select ScoreId from Score
where
(
ScoreId in(select top 2 ScoreId from Score where ScoreType='考核1' and ScoreUser='张三' order by ScoreInfo,ScoreId desc)
or
Scoreid in(select top 2 ScoreId from Score where ScoreType='考核1' and ScoreUser='张三' order by ScoreInfo,ScoreId)
)
and ScoreType='考核1' and ScoreUser='张三'
---结果---
ScoreId
-----------
3
4
8
9
(所影响的行数为 4 行)
---测试数据---
if object_id('[Score]') is not null drop table [Score]
go
create table [Score]([ScoreId] int,[ScoreInfo] int,[ScoreType] varchar(5),[ScoreUser] varchar(4))
insert [Score]
select 3,100,'考核1','张三' union all
select 4,99,'考核1','张三' union all
select 5,97,'考核1','张三' union all
select 6,96,'考核1','张三' union all
select 7,99,'考核1','李四' union all
select 8,95,'考核1','张三' union all
select 9,94,'考核1','张三' union all
select 10,99,'考核2','张三' union all
select 11,99,'考核2','李四'
---查询---
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='张三'
---结果---
ScoreId
-----------
3
4
8
9
(所影响的行数为 4 行)
declare @tb table (scoreid int,scoreinfo int,scoretype nvarchar(10),scoreuser nvarchar(10))
insert into @tb select 3,100,'考核1','张三'
union all select 4,99,'考核1','张三'
union all select 5,97,'考核1','张三'
union all select 6,96,'考核1','张三'
union all select 7,99,'考核1','李四'
union all select 8,95,'考核1','张三'
union all select 9,94,'考核1','张三'
union all select 10,99,'考核2','张三'
union all select 11,99,'考核2','李四'
;with cte as
(
select * from @tb where scoreuser='张三' and scoretype='考核1'
),
cte_1 as
( select * from cte a where scoreinfo in (select top 2 scoreinfo from cte order by scoreinfo)
union all
select * from cte a where scoreinfo in (select top 2 scoreinfo from cte order by scoreinfo desc)
)
select * from cte_1 order by scoreid
--------------
3 100 考核1 张三
4 99 考核1 张三
8 95 考核1 张三
9 94 考核1 张三
--------------