sql去掉重复查询所有数据
例如
(user)表
id name a b
1 张三 1 1
2 张三 2 2
3 李四 3 3
4 李四 4 4
5 李四 5 5
6 王五 6 6
7 赵六 7 7

我想要的效果是
id name a b
1 张三 1 1
3 李四 3 3
6 王五 6 6
7 赵六 7 7

我用过select * from (select DISTINCT name from user) as asd
新手刚刚接触 求指教啊
[解决办法]

SELECT * FROM TB [USER] T WHERE B=(SELECT MAX(B) FROM [USER] WHERE NAME=T.NAME)

[解决办法]

引用:

SELECT * FROM TB [USER] T WHERE B=(SELECT MAX(B) FROM [USER] WHERE NAME=T.NAME)

mark
[解决办法]

select id=MIN(id),name,a=MIN(a),b=MIN(b)
from user 
group by name

[解决办法]
SELECT * FROM TB [USER] T WHERE B=(SELECT MIN(B) FROM [USER] WHERE NAME=T.NAME)

[解决办法]

----------------------------
-- Author  :DBA_Huangzj()
-- Date    :2013-09-06 10:39:57
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)  
 
--Jun 10 2013 20:09:10 
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[user]
if object_id('[user]') is not null drop table [user]
go 
create table [user]([id] int,[name] varchar(4),[a] int,[b] int)
insert [user]
select 1,'张三',1,1 union all
select 2,'张三',2,2 union all
select 3,'李四',3,3 union all
select 4,'李四',4,4 union all
select 5,'李四',5,5 union all
select 6,'王五',6,6 union all
select 7,'赵六',7,7
--------------开始查询--------------------------

select MIN(id) id,name,MIN(a),MIN(b) from [user]
GROUP BY name
ORDER BY id

----------------结果----------------------------
/* 
id          name             
----------- ---- ----------- -----------
1           张三   1           1
3           李四   3           3
6           王五   6           6
7           赵六   7           7

*/

[解决办法]
SORRY 应该是MIN 我都没注意看 结果下面的兄弟们都COPY了