表的字段和内容如下
no name money
1 user1 200
2 user2 300
2 user2 400no 和name是重复的,但是我只想取出其中一个no 和name相同的money,他这里有两个,比如 2 user2 300 和2 user2 400我想写个sql 显示的结果是
1 user1 200
2 user2 300
或者
1 user1 200
2 user2 400
no name money
1 user1 200
2 user2 300
2 user2 400no 和name是重复的,但是我只想取出其中一个no 和name相同的money,他这里有两个,比如 2 user2 300 和2 user2 400我想写个sql 显示的结果是
1 user1 200
2 user2 300
或者
1 user1 200
2 user2 400
[name],[money]
from
tb t
where
not exists(select 1 from tb where [name]=t.[name] and [money]<t.[money])
no=T.NO AND NAME=T. name AND MONEY<T.MONEY)
drop table tcreate table t(
[no] int,
[name] varchar(10),
[money] int
)insert into t
select 1,'user1',200 union all
select 2,'user2',300 union all
select 2,'user2',400 select [no],[name],min([money]) from t group by [no],[name];select [no],[name],max([money]) from t group by [no],[name];
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-17 10:05:33
----------------------------------------------------------------
--> 测试数据:#t
if object_id('tempdb.dbo.#t') is not null drop table #t
go
create table #t([no] int,[name] varchar(5),[money] int)
insert #t
select 1,'user1',200 union all
select 2,'user2',300 union all
select 2,'user2',400
--------------开始查询--------------------------
select [name],[money] from #T a where [money]=(select max([money]) from #T where Name=a.Name) order by [name]
----------------结果----------------------------
/*name money
----- -----------
user1 200
user2 400(所影响的行数为 2 行)
*/
if (Object_ID('t')) is not null
drop table tcreate table t(
[no] int,
[name] varchar(10),
[money] int
)insert into t
select 1,'user1',200 union all
select 2,'user2',300 union all
select 2,'user2',400 select [no],[name],min([money]) [money] from t group by [no],[name];select [no],[name],max([money]) [money] from t group by [no],[name];--------结果----------
no name money
1 user1 200
2 user2 300
no name money
1 user1 200
2 user2 400