类别和刷新时间建联合索引,不要单独建两个索引 查询的时候 where 后面类别和刷新时间两个字段都必须加上
select top 50 * from 表 a where 名称 in (select top 5 名称 from 表 where 类别=a.类别) order by 刷新时间 desc /* 10类别 * 5名称 = top 50 */
--笔误:order by 要放在子查询里才能找出最新刷新的数据 select top 50 * from 表 a where 名称 in (select top 5 名称 from 表 where 类别=a.类别 order by 刷新时间 desc)
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)
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 descunion 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 descunion 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 descunion 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 descunion 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 descunion 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
绝对不正确,先不说语法(语法本身就一堆错误,不仅仅 select 5*),思维就不正确。
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
--上面错了,多了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
楼上的我的要加上 select top 5*
呵呵,这个问题讨论这么激烈啊~~ 是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
晕,没仔细看需求. 这个如何?--来张临时表,测试用 :) 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
查询的时候 where 后面类别和刷新时间两个字段都必须加上
/*
10类别 * 5名称 = top 50
*/
select top 50 * from 表 a where 名称 in (select top 5 名称 from 表 where 类别=a.类别 order by 刷新时间 desc)
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 descunion
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 descunion
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 descunion
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 descunion
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 descunion
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
(
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
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
-- 建造临时表,供测试使用
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
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