if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([商品ID] int,[商品名称] varchar(14),[点击率] int)
insert [tb]
select 9,'魔兽世界(国服)',1 union all
select 10,'魔兽世界(国服)',2 union all
select 11,'魔兽世界(国服)',3 union all
select 12,'魔兽世界(国服)',4 union all
select 13,'魔兽世界(国服)',5 union all
select 14,'魔兽世界(国服)',6 union all
select 15,'魔兽世界(国服)',7 union all
select 16,'梦幻西游',8 union all
select 17,'梦幻西游',9 union all
select 18,'梦幻西游',10 union all
select 19,'梦幻西游',11 union all
select 20,'梦幻西游',12 union all
select 27,'跑跑卡丁车',13 union all
select 28,'跑跑卡丁车',14 union all
select 29,'跑跑卡丁车',15 union all
select 30,'跑跑卡丁车',16 union all
select 31,'跑跑卡丁车',17 union all
select 32,'跑跑卡丁车',18 union all
select 33,'跑跑卡丁车',19 union all
select 34,'地下城与勇士',20 union all
select 35,'地下城与勇士',600 union all
select 36,'地下城与勇士',22 union all
select 38,'地下城与勇士',23 union all
select 39,'地下城与勇士',24 union all
select 40,'地下城与勇士',25 union all
select 41,'永恒之塔',600 union all
select 42,'永恒之塔',600
 
-->查询
select *
from tb t 
where not exists(select 1 from tb where 商品名称=t.商品名称 and 点击率>t.点击率)
order by 3

/**
商品ID        商品名称           点击率
----------- -------------- -----------
15          魔兽世界(国服)       7
20          梦幻西游           12
33          跑跑卡丁车          19
35          地下城与勇士         600
41          永恒之塔           600
42          永恒之塔           600

(6 行受影响)

**/

------解决方案--------------------
create table #tb(商品ID int,商品名称 varchar(20),点击率 int)
insert into #tb
  select '9','魔兽世界（国服）','1'
union all select '10','魔兽世界（国服）','2'
union all select '11','魔兽世界（国服）','3'
union all select '12','魔兽世界（国服）','4'
union all select '13','魔兽世界（国服）','5'
union all select '14','魔兽世界（国服）','6'
union all select '15','魔兽世界（国服）','7'
union all select '16','梦幻西游','8'
union all select '17','梦幻西游','9'
union all select '18','梦幻西游','10'
union all select '19','梦幻西游','11'
union all select '20','梦幻西游','12'
union all select '27','跑跑卡丁车','13'
union all select '28','跑跑卡丁车','14'
union all select '29','跑跑卡丁车','15'
union all select '30','跑跑卡丁车','16'
union all select '31','跑跑卡丁车','17'
union all select '32','跑跑卡丁车','18'
union all select '33','跑跑卡丁车','19'
union all select '34','地下城与勇士','20'
union all select '35','地下城与勇士','600'
union all select '36','地下城与勇士','22'
union all select '38','地下城与勇士','23'
union all select '39','地下城与勇士','24'
union all select '40','地下城与勇士','25'
union all select '41','永恒之塔','600'
union all select '42','永恒之塔','600'


select a.*
from #tb a
inner join (select 商品名称,max(点击率) as 点击率 from #tb group by 商品名称)b
on a.商品名称=b.商品名称 and a.点击率=b.点击率
order by 1

商品ID 商品名称 点击率
--------------------
15	魔兽世界（国服）	7
20	梦幻西游	12
33	跑跑卡丁车	19
35	地下城与勇士	600
41	永恒之塔	600
42	永恒之塔	600






------解决方案--------------------
SQL code