过滤数据sql解决方案

select id,index,max(usedtimes)  as Max_usedtimes from table group by index
------解决方案--------------------
SQL code
select id,[index],max(usedtimes)  as Max_usedtimes from tb group by [index]
------解决方案--------------------
楼上的代码能跑吗？group by index以后还能选出来ID?
------解决方案--------------------
探讨


SQL code
消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'tb.usedtimes无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[index] varchar(4),[usedtimes] int)insert [tb]select 1,'a-01',1 union allselect 2,'a-01',2 union allselect 3,'a-02',1 union allselect 4,'a-03',1 union allselect 5,'a-03',2 union allselect 6,'a-04',1 union allselect 7,'a-04',2goselect * from tb twhere not exists(select 1 from tb where [index]=t.[index] and usedtimes>t.usedtimes)/*id          index usedtimes----------- ----- -----------2           a-01  23           a-02  15           a-03  27           a-04  2(4 行受影响)*/
------解决方案--------------------
SQL code
--orselect * from tb twhere usedtimes=(select max(usedtimes) from tb where [index]=t.[index])order by id--orselect * from tb twhere usedtimes=(select top 1 usedtimes from tb where [index]=t.[index] order by usedtimes desc)order by id/*id          index usedtimes----------- ----- -----------2           a-01  23           a-02  15           a-03  27           a-04  2(4 行受影响)*/
------解决方案--------------------
怎么不看我的
select id,index,max(usedtimes) over(partition by index) as Max_usedtimes from table
------解决方案--------------------
然后再和原表join 一下就行了
with cte as(
select id,index,max(usedtimes) over(partition by index) as Max_usedtimes from table)
select table.* from cte join table
on cte.index=table.index and cte.Max_usedtimes =table.usedtimes
------解决方案--------------------
/*
id index usedtimes
1 a-01 1
2 a-01 2
3 a-02 1
4 a-03 1
5 a-03 2
6 a-04 1
7 a-04 2
我要取出每条数据中usedtimes最大的一条数据，要是就一条，那么就取他
结果：
id index usedtimes
2 a-01 2
3 a-02 1
5 a-03 2
7 a-04 2
*/

go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id varchar(2),
[index] varchar(10),
usedtimes int
)
go
insert tbl
select '1','a-01',1 union all
select '2','a-01',2 union all
select '3','a-02',1 union all
select '4','a-03',1 union all
select '5','a-03',2 union all
select '6','a-04',1 union all
select '7','a-04',2


select id,tbl.[index],a.max_usedtimes from tbl  
inner join(select [index],MAX(usedtimes) as max_usedtimes from tbl group by [index])a