ScoreId ScoreInfo ScoreType ScoreUser
3 100 考核1 张三
4 99 考核1 张三
5 97 考核1 张三
6 96 考核1 张三
7 99 考核1 李四
8 95 考核1 张三
9 94 考核1 张三
10 99 考核2 张三
11 99 考核2 李四
我要查询的结果是
姓名为张三考核类型为考核1的2个最高记录和2个最低记录,但用我的查询语句确查不出来! 即查询结果为
3
4
8
9 上回的答案有点疏漏,就是如果分数(字段名为ScoreInfo)大部分一样或是完全一样的时候,查出来的结果就错了,该怎么改了,谁会了
3 100 考核1 张三
4 99 考核1 张三
5 97 考核1 张三
6 96 考核1 张三
7 99 考核1 李四
8 95 考核1 张三
9 94 考核1 张三
10 99 考核2 张三
11 99 考核2 李四
我要查询的结果是
姓名为张三考核类型为考核1的2个最高记录和2个最低记录,但用我的查询语句确查不出来! 即查询结果为
3
4
8
9 上回的答案有点疏漏,就是如果分数(字段名为ScoreInfo)大部分一样或是完全一样的时候,查出来的结果就错了,该怎么改了,谁会了
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 行)
josy 这个我试过了,如果所有记录都一样的话,就是错误的
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 行)
这样呢?
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
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 张三
--------------