分组查询

lsj_zrp 2008-07-15 01:22:21
Name Number
SH 10
SH 20
SH 30
SH 40
BJ 50
BJ 60
BJ 70
BJ 80
GZ 90
GZ 100
GZ 110
GZ 120

如何查询出下面的结果
GZ 100
GZ 110
GZ 120
BJ 60
BJ 70
BJ 80
SH 20
SH 30
SH 40
也就是取每个地区的前三个,地区个数不定。谢谢
...全文
160 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
nzperfect 2008-07-15
  • 打赏
  • 举报
回复
--> 测试数据: #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
*/
whw502 2008-07-15
  • 打赏
  • 举报
回复
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
nzperfect 2008-07-15
  • 打赏
  • 举报
回复
--> 测试数据: #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

;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 20
SH 30
SH 40
*/
wzy_love_sly 2008-07-15
  • 打赏
  • 举报
回复
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
)


name number
SH 20
SH 30
SH 40
BJ 60
BJ 70
BJ 80
GZ 100
GZ 110
GZ 120
liangCK 2008-07-15
  • 打赏
  • 举报
回复
--> 测试数据: #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 行受影响)

*/
中国风 2008-07-15
  • 打赏
  • 举报
回复
 
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 個資料列受到影響)


2000時可用
hery2002 2008-07-15
  • 打赏
  • 举报
回复
-->生成测试数据

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
*/
lgxyz 2008-07-15
  • 打赏
  • 举报
回复
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 行)

*/
中国风 2008-07-15
  • 打赏
  • 举报
回复
 
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 個資料列受到影響)


昵称被占用了 2008-07-15
  • 打赏
  • 举报
回复
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
liangCK 2008-07-15
  • 打赏
  • 举报
回复
select *
from tb t
where 3>(select count(*) from tb where t.name=name and number<t.number)

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧