求SQL语句

[buaawjh]

有这么一张表，
名称 类别 刷新时间

我想罗列出最新刷新的10个类别，以及每个类别下的5个名称

类别和刷新时间有索引，请问如何高效检索？谢谢各位！:)

[hb_gx]

类别和刷新时间建联合索引，不要单独建两个索引
查询的时候 where 后面类别和刷新时间两个字段都必须加上

[buaawjh]

求SQL语句

[Limpire]

select top 50 * from 表 a where 名称 in (select top 5 名称 from 表 where 类别=a.类别) order by 刷新时间 desc
/*
10类别 * 5名称 = top 50
*/

[buaawjh]

结果不对，有的类别有8条记录

[Limpire]

--笔误：order by 要放在子查询里才能找出最新刷新的数据
select top 50 * from 表 a where 名称 in (select top 5 名称 from 表 where 类别=a.类别 order by 刷新时间 desc)

[Limpire]

结果不对，有的类别有8条记录
------------------
那是因为名称有重复的。首先我要知道你是要重复还是不重复

[buaawjh]

我要的结果是：1、首先罗列出最新刷新的十个类，2、1检索出来的每个类下最新刷新的5个名称

[Limpire]

select top 50 * from (select 名称,类别,刷新时间=max(刷新时间) from 表 group by 名称,类别) a where 名称 in (select top 5 名称 from ((select 名称,类别,刷新时间=max(刷新时间) from 表 group by 名称,类别)) b where b.类别=a.类别 order by b.刷新时间 desc)

[qiuming0306]

select 5* from a where 类别 =( select top 1 类别 from 表 a group by 类别 order by 刷新时间 desc) order by desc
union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 1 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc
union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 2 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc

union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 3 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc

union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 4 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc


union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 5 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc
union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 6 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc

union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 7 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc

union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 8 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc

union
select 5* from a where 类别 =( select top 1 类别 from 表 a where 类别 not in (select top 9 类别 from 表 a group by 类别 order by 刷新时间 desc)) order by desc

[qiuming0306]

绝对正确的方法，就是有些麻烦，我在看看有没有简单的写法

[Limpire]

绝对不正确，先不说语法（语法本身就一堆错误，不仅仅 select 5*），思维就不正确。

[dawugui]

select * from
(
select * from tb as t
where (select count(*) from tb where 类别 = t.类别 and 刷新时间 < t.刷新时间) < 5
) m
where 类别 in
(
select top 10 a.类别 into temp from tb a,
(select 类别,max(刷新时间) 刷新时间 from tb gorup by 类别) b
where a.类别 = b.类别 and a.刷新时间 = b.刷新时间
)
order by m.类别,m.刷新时间 desc

[dawugui]

--上面错了,多了into temp
select * from
(
select * from tb as t
where (select count(*) from tb where 类别 = t.类别 and 刷新时间 < t.刷新时间) < 5
) m
where 类别 in
(
select top 10 a.类别 from tb a,
(select 类别,max(刷新时间) 刷新时间 from tb gorup by 类别) b
where a.类别 = b.类别 and a.刷新时间 = b.刷新时间
)
order by m.类别,m.刷新时间 desc

[qiuming0306]

楼上的我的要加上 select top 5*

[qiuming0306]

我使自己手写的，呵呵，因为英语不好，总写错这个，要看的是方法思路

[qiuming0306]

我的哪里不对了，首先找到排名的前10个组，在分别查找每组的前5个，然后连接起来

[randy_df]

呵呵，这个问题讨论这么激烈啊~~ 是SQL2005吗?里面有很简单的方法，呵呵
-- 建造临时表，供测试使用
SELECT * INTO 临时表
FROM(
SELECT 'n1' as 名称,'T1' as 类别,GETDATE()-1 as 刷新时间
UNION ALL
SELECT 'n2' as 名称,'T1' as 类别,GETDATE()-1 as 刷新时间
UNION ALL
SELECT 'n3' as 名称,'T1' as 类别,GETDATE()-1 as 刷新时间
UNION ALL
SELECT 'n1' as 名称,'T2' as 类别,GETDATE()-2 as 刷新时间
UNION ALL
SELECT 'n2' as 名称,'T2' as 类别,GETDATE()-2 as 刷新时间
UNION ALL
SELECT 'n3' as 名称,'T2' as 类别,GETDATE()-2 as 刷新时间)T

--SQL 主体，只有几行
SELECT * FROM(
SELECT
类别, 名称, 刷新时间,
ROW_NUMBER()
OVER (
PARTITION BY 类别
order by 名称 ASC
) as RANK
FROM 临时表)T
WHERE RANK <11
ORDER BY 刷新时间 DESC

[randy_df]

晕,没仔细看需求. 这个如何?

--来张临时表,测试用 :)
if exists (select * from dbo.sysobjects where id = object_id(N'[临时表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [临时表]
SELECT * INTO 临时表
FROM(
SELECT 'n1' as 名称,'T1' as 类别,GETDATE()-3 as 刷新时间
UNION ALL
SELECT 'n2' as 名称,'T1' as 类别,GETDATE()-3 as 刷新时间
UNION ALL
SELECT 'n3' as 名称,'T1' as 类别,GETDATE()-3 as 刷新时间
UNION ALL
SELECT 'n1' as 名称,'T2' as 类别,GETDATE()-5 as 刷新时间
UNION ALL
SELECT 'n2' as 名称,'T2' as 类别,GETDATE()-5 as 刷新时间
UNION ALL
SELECT 'n3' as 名称,'T2' as 类别,GETDATE()-5 as 刷新时间
UNION ALL
SELECT 'n1' as 名称,'T3' as 类别,GETDATE()-2 as 刷新时间
UNION ALL
SELECT 'n2' as 名称,'T3' as 类别,GETDATE()-2 as 刷新时间
UNION ALL
SELECT 'n3' as 名称,'T3' as 类别,GETDATE()-2 as 刷新时间
)T

--SQL 正文,多了几行 :)
SELECT *
FROM(
SELECT
类别, 名称, 刷新时间,
ROW_NUMBER() OVER (
PARTITION BY 类别 order by 名称 ASC
) as RANK
FROM 临时表
)T
WHERE RANK < 3 -- 如果每组取前5个就改成[6]
AND EXISTS(
SELECT 1 FROM(
SELECT TOP 2 -- 如果取前10个类别就添[10]
类别
FROM(
SELECT DISTINCT 类别,刷新时间 FROM 临时表)B
ORDER BY 刷新时间 DESC
)TC
WHERE T.类别 = TC.类别)
ORDER BY 刷新时间 DESC