求一SQL语句

[pzhuyy]

有一表,如下所示:
id(int),mid(varchar(20)),val(int)
记录如下:
1,1330,12
2,1330,14
3,1330,15
4,1331,0
5,1331,2
6,1335,13
.....
得结果如下:
3,1330,15
5,1331,2
6,1335,13
即去重复的同时保留val的最大值.
分不够再加,不胜感激......

[橡胶轮胎行业数字化高总]

select max(id),mid,max(val) from table1
group by mid

[wwh999]

delete A from 表A where A.val<>(select max(val) from 表 where mid=A.mid)

[云中客]

select max(id),mid,max(val) from table1 group by mid
应该就是这样

[jixiaojie]

select (select top 1 id from tb1 where mid=a.mid and val=max(a.val)) as id ,mid,max(val) as val from tb1 a
group by mid

[LouisXIV]

有点败家^^

[wwh999]

如果你僅僅是需要一個查詢結果,不用從表中刪除數據,這樣就可以了:

select min(id),min(mid),max(val) from 表
group by mid
order by id

這里對select列表中的列id,mid,進行min取值都是沒有什麼必要的,但是因為語法需要需要這樣寫,用MAX也行.

[liangpei2008]

Declare @t table(id int,mid varchar(20),val int)
Insert @t Select 1,'1330',12
Union all Select 2,'1330',14
Union all Select 3,'1330',15
Union all Select 4,'1331',0
Union all Select 5,'1331',2
Union all Select 6,'1335',13
------------------
Select * From @t A
Where Not Exists( Select 1 From @t Where mid=A.mid and val>A.val)

[liangpei2008]

--Or
Select * From @t A
Where id in ( Select top 1 id From @t Where mid=A.mid order by val desc)

[liangpei2008]

不过这两个相关子查询效率都不高！能分组取最大值就用分组！

[wwh999]

if exists(select * from sysobjects where name='Tb' and xtype='U') drop table Tb
GO
--生成测试用数据,为SELECT初始环境
CREATE TABLE Tb(ID int,mid varchar(20),val int)
INSERT INTO Tb SELECT 1,'1330',12
UNION ALL SELECT 2,'1330',14
UNION ALL SELECT 3,'1330',15
UNION ALL SELECT 4,'1331',0
UNION ALL SELECT 5,'1331',2
UNION ALL SELECT 6,'1335',13
--select * from Tb
GO
--1>僅為查找
select id=min(id),mid,max(val) from Tb
group by mid
order by min(id)

--2>從表中刪除,在表僅保留最大值!
delete A from Tb A where A.val<>(select max(val) from Tb where mid=A.mid)
select * from Tb

GO
--删除测试散数据
DROP TABLE Tb

/* -- 结果 --

id mid
----------- -------------------- -----------
1 1330 15
4 1331 2
6 1335 13

（所影响的行数为 3 行）


（所影响的行数为 3 行）

ID mid val
----------- -------------------- -----------
3 1330 15
5 1331 2
6 1335 13

（所影响的行数为 3 行）


*/ -- 完成 --

[pzhuyy]

呵呵,谢谢大家,可以了..