select * from tb where num >= (select min(num) from tb) and num <=(select top 1 num from tb as a where 5=(select count(distinct num) from tb where a.num <= num))
--> 生成测试数据: @tb DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9--SQL查询如下:select * from @tb where num >= (select min(num) from @tb) and num <=(select top 1 num from @tb as a where 5=(select count(distinct num) from @tb where a.num >= num))/* id num ---- ----------- 01 1 02 9 03 11 04 2 05 7 06 2 08 11 09 9(8 行受影响) */
--> 生成测试数据: @tb DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9--SQL查询如下:select * from @tb where num > (select min(num) from @tb) and num <(select top 1 num from @tb as a where 5=(select count(distinct num) from @tb where a.num >= num))/* id num ---- ----------- 02 9 04 2 05 7 06 2 09 9(5 行受影响)*/
DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9SELECT ID,NUM,(SELECT COUNT(DISTINCT NUM) FROM @TB WHERE NUM<=T.NUM )AS NEWNUM INTO #T FROM @TB TSELECT ID,NUM FROM #T WHERE NEWNUM > 1 AND NEWNUM<5ID NUM ---- ----------- 02 9 04 2 05 7 06 2 09 9(所影响的行数为 5 行)
select * from tb where num>=(select min(num) from tb) and num<=(select top 1 num from tb where num not in(select top 4 num from tb order by num ))
--> 生成测试数据: @tb DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9--SQL查询如下:select id,num from (select rowid=dense_rank() over(order by num),* from @tb ) as t where rowid between 2 and 4/* id num ---- ----------- 04 2 06 2 05 7 02 9 09 9(5 行受影响) */
DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9 declare @n1 int ,@n2 int set @n1=1 set @n2=5 select id,num from (select dense_rank()over(order by num) as rn ,* from @tb ) k where rn between @n1+1 and @n2-1id num ---- ----------- 04 2 06 2 05 7 02 9 09 9
DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9 declare @n1 int ,@n2 int set @n1=1 set @n2=5 select COUNT(*) as 个数 from (select dense_rank()over(order by num) as rn ,* from @tb ) k where rn between @n1+1 and @n2-1(9 行受影响) 个数 ----------- 5
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-10-28 20:39:48 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] varchar(2),[num] int) insert [tb] select '01',1 union all select '02',9 union all select '03',11 union all select '04',2 union all select '05',7 union all select '06',2 union all select '07',13 union all select '08',11 union all select '09',9 --------------开始查询-------------------------- select id from tb where num>(select min(num) from tb) and num<=(select top 1 num from tb where num not in(select top 4 num from tb order by num )) ----------------结果---------------------------- /* id ---- 02 04 05 06 09(5 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-10-28 20:39:48 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] varchar(2),[num] int) insert [tb] select '01',1 union all select '02',9 union all select '03',11 union all select '04',2 union all select '05',7 union all select '06',2 union all select '07',13 union all select '08',11 union all select '09',9 --------------开始查询-------------------------- select count(1) from tb where num>(select min(num) from tb) and num<=(select top 1 num from tb where num not in(select top 4 num from tb order by num )) ----------------结果---------------------------- /*----------- 5(1 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-10-28 20:39:48 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] varchar(2),[num] int) insert [tb] select '01',1 union all select '02',9 union all select '03',11 union all select '04',2 union all select '05',7 union all select '06',2 union all select '07',13 union all select '08',11 union all select '09',9 --------------开始查询-------------------------- select id from tb where num>(select min(num) from tb) and num<(select top 1 num from tb where num not in(select top 5 num from tb order by num )) ----------------结果---------------------------- /*----------- 5(1 行受影响) */
--> 生成测试数据: @tb DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9--SQL查询如下:select (select min(num) from @tb), (select top 1 num from @tb as a where 5=(select count(distinct num) from @tb where a.num >= num)), count(*) from @tb where num > (select min(num) from @tb) and num <(select top 1 num from @tb as a where 5=(select count(distinct num) from @tb where a.num >= num))
DECLARE @tb TABLE (id varchar(2),num int) INSERT INTO @tb SELECT '01',1 UNION ALL SELECT '02',9 UNION ALL SELECT '03',11 UNION ALL SELECT '04',2 UNION ALL SELECT '05',7 UNION ALL SELECT '06',2 UNION ALL SELECT '07',13 UNION ALL SELECT '08',11 UNION ALL SELECT '09',9SELECT ID,NUM,(SELECT COUNT(DISTINCT NUM) FROM @TB WHERE NUM<=T.NUM )AS NEWNUM INTO #T FROM @TB TSELECT MIN(NUM ),MAX(NUM), (SELECT COUNT(*) FROM #T WHERE NEWNUM>1 AND NEWNUM<5) FROM #T WHERE NEWNUM >= 1 AND NEWNUM<=5 ----------- ----------- ----------- 1 11 5(所影响的行数为 1 行)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-10-28 20:39:48 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] varchar(2),[num] int) insert [tb] select '01',1 union all select '02',9 union all select '03',11 union all select '04',2 union all select '05',7 union all select '06',2 union all select '07',13 union all select '08',11 union all select '09',9 --------------开始查询-------------------------- select (select min(num) from tb) as [min], (select top 1 num from tb where num not in(select top 5 num from tb order by num )) as [max], count(1) as num from tb where num>(select min(num) from tb) and num<(select top 1 num from tb where num not in(select top 5 num from tb order by num )) ----------------结果---------------------------- /*min max num ----------- ----------- ----------- 1 11 5(1 行受影响)*/
declare @table table (id nvarchar(4),num int) insert into @table select '01', 1 union all select '02' , 9 union all select '03' , 11 union all select '04' , 2 union all select '05' , 7 union all select '06' , 2 union all select '07' , 13 union all select '08' , 11 union all select '09' , 9 select min(num) as min ,max(num) as max ,count(*) as num from (select * from @table where num > (select min(num) from @table) and num < (select top 1 num from @table as a where 5=(select count(distinct num) from @table where a.num >= num))) h -------------------- 2 9 5
declare @table table (id nvarchar(4),num int) insert into @table select '01', 1 union all select '02' , 9 union all select '03' , 11 union all select '04' , 2 union all select '05' , 7 union all select '06' , 2 union all select '07' , 13 union all select '08' , 11 union all select '09' , 9 select min(num) as min ,max(num) as max ,count(*) as num from (select * from @table where num > (select min(num) from @table) and num < (select top 1 num from @table as a where 5=(select count(distinct num) from @table where a.num >= num))) h -------------------- 2 9 5
from tb
where num >= (select min(num) from tb)
and num <=(select top 1 num
from tb as a
where 5=(select count(distinct num)
from tb
where a.num <= num))
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-28 20:30:45
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id varchar(2),num int)
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9--SQL查询如下:select *
from @tb
where num >= (select min(num) from @tb)
and num <=(select top 1 num
from @tb as a
where 5=(select count(distinct num)
from @tb
where a.num >= num))/*
id num
---- -----------
01 1
02 9
03 11
04 2
05 7
06 2
08 11
09 9(8 行受影响)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-28 20:30:45
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id varchar(2),num int)
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9--SQL查询如下:select *
from @tb
where num > (select min(num) from @tb)
and num <(select top 1 num
from @tb as a
where 5=(select count(distinct num)
from @tb
where a.num >= num))/*
id num
---- -----------
02 9
04 2
05 7
06 2
09 9(5 行受影响)*/
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9SELECT ID,NUM,(SELECT COUNT(DISTINCT NUM) FROM @TB WHERE NUM<=T.NUM )AS NEWNUM INTO #T FROM @TB TSELECT ID,NUM FROM #T WHERE NEWNUM > 1 AND NEWNUM<5ID NUM
---- -----------
02 9
04 2
05 7
06 2
09 9(所影响的行数为 5 行)
*
from
tb
where
num>=(select min(num) from tb)
and
num<=(select top 1 num from tb where num not in(select top 4 num from tb order by num ))
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-28 20:30:45
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id varchar(2),num int)
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9--SQL查询如下:select id,num
from (select rowid=dense_rank() over(order by num),* from @tb ) as t
where rowid between 2 and 4/*
id num
---- -----------
04 2
06 2
05 7
02 9
09 9(5 行受影响)
*/
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9
declare @n1 int ,@n2 int
set @n1=1
set @n2=5
select id,num
from
(select dense_rank()over(order by num) as rn ,* from @tb ) k
where rn between @n1+1 and @n2-1id num
---- -----------
04 2
06 2
05 7
02 9
09 9
DECLARE @tb TABLE (id varchar(2),num int)
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9
declare @n1 int ,@n2 int
set @n1=1
set @n2=5
select COUNT(*) as 个数
from
(select dense_rank()over(order by num) as rn ,* from @tb ) k
where rn between @n1+1 and @n2-1(9 行受影响)
个数
-----------
5
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-28 20:39:48
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[num] int)
insert [tb]
select '01',1 union all
select '02',9 union all
select '03',11 union all
select '04',2 union all
select '05',7 union all
select '06',2 union all
select '07',13 union all
select '08',11 union all
select '09',9
--------------开始查询--------------------------
select
id
from
tb
where
num>(select min(num) from tb)
and
num<=(select top 1 num from tb where num not in(select top 4 num from tb order by num ))
----------------结果----------------------------
/* id
----
02
04
05
06
09(5 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-28 20:39:48
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[num] int)
insert [tb]
select '01',1 union all
select '02',9 union all
select '03',11 union all
select '04',2 union all
select '05',7 union all
select '06',2 union all
select '07',13 union all
select '08',11 union all
select '09',9
--------------开始查询--------------------------
select
count(1)
from
tb
where
num>(select min(num) from tb)
and
num<=(select top 1 num from tb where num not in(select top 4 num from tb order by num ))
----------------结果----------------------------
/*-----------
5(1 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-28 20:39:48
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[num] int)
insert [tb]
select '01',1 union all
select '02',9 union all
select '03',11 union all
select '04',2 union all
select '05',7 union all
select '06',2 union all
select '07',13 union all
select '08',11 union all
select '09',9
--------------开始查询--------------------------
select
id
from
tb
where
num>(select min(num) from tb)
and
num<(select top 1 num from tb where num not in(select top 5 num from tb order by num ))
----------------结果----------------------------
/*-----------
5(1 行受影响)
*/
1 11 5
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-28 20:30:45
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id varchar(2),num int)
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9--SQL查询如下:select (select min(num) from @tb),
(select top 1 num
from @tb as a
where 5=(select count(distinct num)
from @tb
where a.num >= num)),
count(*)
from @tb
where num > (select min(num) from @tb)
and num <(select top 1 num
from @tb as a
where 5=(select count(distinct num)
from @tb
where a.num >= num))
INSERT INTO @tb
SELECT '01',1 UNION ALL
SELECT '02',9 UNION ALL
SELECT '03',11 UNION ALL
SELECT '04',2 UNION ALL
SELECT '05',7 UNION ALL
SELECT '06',2 UNION ALL
SELECT '07',13 UNION ALL
SELECT '08',11 UNION ALL
SELECT '09',9SELECT ID,NUM,(SELECT COUNT(DISTINCT NUM) FROM @TB WHERE NUM<=T.NUM )AS NEWNUM INTO #T FROM @TB TSELECT MIN(NUM ),MAX(NUM),
(SELECT COUNT(*) FROM #T WHERE NEWNUM>1 AND NEWNUM<5) FROM #T WHERE NEWNUM >= 1 AND NEWNUM<=5
----------- ----------- -----------
1 11 5(所影响的行数为 1 行)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-28 20:39:48
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[num] int)
insert [tb]
select '01',1 union all
select '02',9 union all
select '03',11 union all
select '04',2 union all
select '05',7 union all
select '06',2 union all
select '07',13 union all
select '08',11 union all
select '09',9
--------------开始查询--------------------------
select
(select min(num) from tb) as [min],
(select top 1 num from tb where num not in(select top 5 num from tb order by num )) as [max],
count(1) as num
from
tb
where
num>(select min(num) from tb)
and
num<(select top 1 num from tb where num not in(select top 5 num from tb order by num ))
----------------结果----------------------------
/*min max num
----------- ----------- -----------
1 11 5(1 行受影响)*/
declare @table table (id nvarchar(4),num int)
insert into @table
select '01', 1 union all
select '02' , 9 union all
select '03' , 11 union all
select '04' , 2 union all
select '05' , 7 union all
select '06' , 2 union all
select '07' , 13 union all
select '08' , 11 union all
select '09' , 9
select min(num) as min ,max(num) as max ,count(*) as num from
(select * from @table
where num > (select min(num) from @table)
and num < (select top 1 num
from @table as a
where 5=(select count(distinct num)
from @table
where a.num >= num))) h
--------------------
2 9 5
declare @table table (id nvarchar(4),num int)
insert into @table
select '01', 1 union all
select '02' , 9 union all
select '03' , 11 union all
select '04' , 2 union all
select '05' , 7 union all
select '06' , 2 union all
select '07' , 13 union all
select '08' , 11 union all
select '09' , 9
select min(num) as min ,max(num) as max ,count(*) as num from
(select * from @table
where num > (select min(num) from @table)
and num < (select top 1 num
from @table as a
where 5=(select count(distinct num)
from @table
where a.num >= num))) h
--------------------
2 9 5