27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Name varchar(2),Number int)
insert into #T
select 'SH',10 union all
select 'SH',20 union all
select 'SH',30 union all
select 'SH',30 union all
select 'SH',40 union all
select 'BJ',50 union all
select 'BJ',60 union all
select 'BJ',70 union all
select 'BJ',80 union all
select 'GZ',90 union all
select 'GZ',100 union all
select 'GZ',110 union all
select 'GZ',120
;with t as (select row_number() over (order by name,number desc) nid,* from #T)
select name,number from t a where nid in (select top 3 nid from t where name=a.name order by nid asc) order by name,number
/*
BJ 60
BJ 70
BJ 80
GZ 100
GZ 110
GZ 120
SH 30
SH 30
SH 40
*/
create table #temp
(id int ,
length int,
d1 int,
d2 int)
insert into #temp
select 1 , 0 , 223 ,322
union all
select 1 ,100 , 321 ,321
union all
select 1 ,900 , 321 ,213
union all
select 1 ,1000 , 321 ,213
union all
select 2 ,20 , 321 ,321
union all
select 2 ,322 , 321 ,213
union all
select 2 ,1233 , 321 ,213
union all
select 3 ,1 , 321 ,213
union all
select 3 ,100 , 321 ,213
union all
select 4 ,20 , 321 ,213
union all
select 4 ,40 , 321 ,213
union all
select 4 ,100 , 321 ,213
declare @nub int
set @nub=3
select id,length from #temp T0 where (select count(id) from #temp where length<T0.length and id=T0.id)<@nub
drop table #temp
declare @tb table(name varchar(50),number int)
insert into @tb select 'SH',10
insert into @tb select 'SH',20
insert into @tb select 'SH',30
insert into @tb select 'SH',40
insert into @tb select 'BJ',50
insert into @tb select 'BJ',60
insert into @tb select 'BJ',70
insert into @tb select 'BJ',80
insert into @tb select 'GZ',90
insert into @tb select 'GZ',100
insert into @tb select 'GZ',110
insert into @tb select 'GZ',120
select * from @tb t where exists(
select 1 from @tb where name=t.name and number<t.number
group by name having count(1)<=3
)
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Name varchar(2),Number int)
insert into #T
select 'SH',10 union all
select 'SH',20 union all
select 'SH',30 union all
select 'SH',40 union all
select 'BJ',50 union all
select 'BJ',60 union all
select 'BJ',70 union all
select 'BJ',80 union all
select 'GZ',90 union all
select 'GZ',100 union all
select 'GZ',110 union all
select 'GZ',120
select *
from #T t
where 3>(select count(*) from #T where t.name=name and number>t.number)
order by number desc
select distinct t1.*
from #T t
cross apply (select top 3 * from #T where t.name=name order by number desc) t1
/*
Name Number
---- -----------
GZ 120
GZ 110
GZ 100
BJ 80
BJ 70
BJ 60
SH 40
SH 30
SH 20
(9 行受影响)
*/
declare @T table([Name] nvarchar(2),[Number] int)
Insert @T
select N'SH',10 union all
select N'SH',20 union all
select N'SH',30 union all
select N'SH',40 union all
select N'BJ',50 union all
select N'BJ',60 union all
select N'BJ',70 union all
select N'BJ',80 union all
select N'GZ',90 union all
select N'GZ',100 union all
select N'GZ',110 union all
select N'GZ',120
select
*
from
@T a
WHERE
checksum([Name],[Number]) in (SELECT top 3 checksum([Name],[Number])from @T where [Name]=a.[Name] order by [Number] desc )
(12 個資料列受到影響)
Name Number
---- -----------
SH 20
SH 30
SH 40
BJ 60
BJ 70
BJ 80
GZ 100
GZ 110
GZ 120
(9 個資料列受到影響)
-->生成测试数据
declare @tb table([Name] nvarchar(2),[Number] int)
Insert @tb
select N'SH',10 union all
select N'SH',20 union all
select N'SH',30 union all
select N'SH',40 union all
select N'BJ',50 union all
select N'BJ',60 union all
select N'BJ',70 union all
select N'BJ',80 union all
select N'GZ',90 union all
select N'GZ',100 union all
select N'GZ',110 union all
select N'GZ',120
Select * from @tb t
where (select count(1) from @tb where [Name] = t.[Name] and [Number]>=t.[Number])<=3
/*
Name Number
---- -----------
SH 20
SH 30
SH 40
BJ 60
BJ 70
BJ 80
GZ 100
GZ 110
GZ 120
*/
DECLARE @TB TABLE(Name VARCHAR(4), Number INT)
INSERT INTO @TB SELECT 'SH', 10
UNION ALL SELECT 'SH', 20
UNION ALL SELECT 'SH', 30
UNION ALL SELECT 'SH', 40
UNION ALL SELECT 'BJ', 50
UNION ALL SELECT 'BJ', 60
UNION ALL SELECT 'BJ', 70
UNION ALL SELECT 'BJ', 80
UNION ALL SELECT 'GZ', 90
UNION ALL SELECT 'GZ', 100
UNION ALL SELECT 'GZ', 110
UNION ALL SELECT 'GZ', 120
SELECT * FROM @TB A
WHERE
(SELECT COUNT(*) FROM @TB WHERE Number>=A.Number AND NAME=A.NAME)<=3
/*
Name Number
---- -----------
SH 20
SH 30
SH 40
BJ 60
BJ 70
BJ 80
GZ 100
GZ 110
GZ 120
(所影响的行数为 9 行)
*/
declare @T table([Name] nvarchar(2),[Number] int)
Insert @T
select N'SH',10 union all
select N'SH',20 union all
select N'SH',30 union all
select N'SH',40 union all
select N'BJ',50 union all
select N'BJ',60 union all
select N'BJ',70 union all
select N'BJ',80 union all
select N'GZ',90 union all
select N'GZ',100 union all
select N'GZ',110 union all
select N'GZ',120
select b.*
from
(Select [Name] from @T group by [Name])a
cross apply
(select top 3 * from @T where [Name]=a.[Name] order by [Number] desc)b
(12 個資料列受到影響)
Name Number
---- -----------
BJ 80
BJ 70
BJ 60
GZ 120
GZ 110
GZ 100
SH 40
SH 30
SH 20
(9 個資料列受到影響)
SELECT * FROM TAB A
WHERE Number IN (SELECT TOP 3 Number FROM TAB WHERE BANE=A.NAME ORDER BY Number DESC)
ORDER BY NAME,Number
select *
from tb t
where 3>(select count(*) from tb where t.name=name and number<t.number)