create table news
(
id int identity(1,1), --新闻编号
cid int, --版块ID
htips int --点击率
)
insert into news values(1,56)
insert into news values(1,67)
insert into news values(1,78)
insert into news values(2,65)
insert into news values(2,67)
insert into news values(3,123)
insert into news values(3,67)select * from news--测试数据不改,不能定义变量和子查询,要求查询每个版块的最高点击率,要求查询结果中包含id
(
id int identity(1,1), --新闻编号
cid int, --版块ID
htips int --点击率
)
insert into news values(1,56)
insert into news values(1,67)
insert into news values(1,78)
insert into news values(2,65)
insert into news values(2,67)
insert into news values(3,123)
insert into news values(3,67)select * from news--测试数据不改,不能定义变量和子查询,要求查询每个版块的最高点击率,要求查询结果中包含id
(
id int identity(1,1), --新闻编号
cid int, --版块ID
htips int --点击率
)
insert into news values(1,56)
insert into news values(1,67)
insert into news values(1,78)
insert into news values(2,65)
insert into news values(2,67)
insert into news values(3,123)
insert into news values(3,67) select cid,MAX(htips) as 最高点击率
from news
group by cid
/*
cid 最高点击率
----------- -----------
1 78
2 67
3 123
*/
from news a,(select cid,max(htips) as htips from news group by cid) b
where a.cid=b.cid and a.htips=b.htips
order by id/**
id cid htips
----------- ----------- -----------
3 1 78
5 2 67
6 3 123(所影响的行数为 3 行)**/