表的结构为
id title hits
1 a 5
2 a 0
3 b 4
4 c 6
5 a 0
6 a 5我想把不一样的title 记录查询出来,按hits倒序排列,title只取hits最大的,hits如果一样只取其中任意一个。
结果为
4 c 6
1 a 5
3 b 4
查询语句应该怎么写?
sql语句应该怎么写?
id title hits
1 a 5
2 a 0
3 b 4
4 c 6
5 a 0
6 a 5我想把不一样的title 记录查询出来,按hits倒序排列,title只取hits最大的,hits如果一样只取其中任意一个。
结果为
4 c 6
1 a 5
3 b 4
查询语句应该怎么写?
sql语句应该怎么写?
create table tb(id int,title varchar(10),hits int)
insert into tb(id,title,hits) select 1 , 'a' , 5
insert into tb(id,title,hits) select 2 , 'a' , 0
insert into tb(id,title,hits) select 3 , 'b' , 4
insert into tb(id,title,hits) select 4 , 'c' , 6
insert into tb(id,title,hits) select 5 , 'a' , 0
insert into tb(id,title,hits) select 6 , 'a' , 5 select A.* from tb A where not exists (select 1 from tb B where A.title = B.title and A.id > B.id )
order by A.hits desc/*
id title hits
4 c 6
1 a 5
3 b 4*/drop table tb
--> 测试数据: @s
declare @s table (id int,title varchar(1),hits int)
insert into @s
select 1,'a',5 union all
select 2,'a',0 union all
select 3,'b',4 union all
select 4,'c',6 union all
select 5,'a',0 union all
select 6,'a',5select * from @s a where not exists(select 1 from @s where title=a.title and hits>=a.hits and id<a.id)
where not exists (
select * from @s where title=a.title
and hits>a.hits or (hits=a.hits and id<a.id) )
select Max(ID),title,hits from tb group by title,hits)
a inner join (
select distinct title,Max(hits) from tb group by title ) b on a.title=b.title and a.hits=b.hits
[id] [int] NULL,
[title] [nchar](10) NULL,
[hits] [int] NULL
) ON [PRIMARY]
插入数据
...以下是查询语句:select max(dbo.TitleTest.id) as id, dbo.TitleTest.title,max(dbo.TitleTest.hits) as hits
from dbo.TitleTest,
(
select
title,
max(hits) as maxhits
from dbo.TitleTest
group by title
) as KS(p_title,p_hits)
where
dbo.TitleTest.title=KS.p_title and dbo.TitleTest.hits=KS.p_hits
group by dbo.TitleTest.title
order by hits desc结果:
4 c 6
6 a 5
3 b 4