表T如下:
字段A(varchar) 字段B(float) 字段C(Datetime)
a 100 2007-1-1
a 50 2007-1-1
b 30 2007-1-1
b 20 2007-1-2
c 50 2007-1-3
c 100 2007-1-4
-------------------------------------
得到如下结果:
字段A 字段B 字段C
a 100 2007-1-1
b 20 2007-1-2
c 100 2007-1-4描述:
1 最后结果集,字段A必须唯一
2 字段A相同的,最字段C最大的那条记录
3 字段A相同的,并且字段C也相同,那么取字段B最大的记录非常感谢大家!!
字段A(varchar) 字段B(float) 字段C(Datetime)
a 100 2007-1-1
a 50 2007-1-1
b 30 2007-1-1
b 20 2007-1-2
c 50 2007-1-3
c 100 2007-1-4
-------------------------------------
得到如下结果:
字段A 字段B 字段C
a 100 2007-1-1
b 20 2007-1-2
c 100 2007-1-4描述:
1 最后结果集,字段A必须唯一
2 字段A相同的,最字段C最大的那条记录
3 字段A相同的,并且字段C也相同,那么取字段B最大的记录非常感谢大家!!
select t1.a,max(t.b) b,t1.c from tmp1 t,
(select a,max(c) c from tmp1 group by a) t1 where t.a=t1.a and t.c=t1.c
group by t1.a,t1.c
--查询结果
A B C
a 100 2007-1-1
b 20 2007-1-2
c 100 2007-1-4
(select a,max(c) as c from tmp1 group by a) as t1 on tmp1.a=t1.a and tmp1.c=t1.c
group by t1.a,t1.c
create table Test
(
A nvarchar(50),
B int,
C datetime
)
insert into Test
SELECT 'a',100,'2007-1-1'
UNION ALL select 'a',50,'2007-1-1'
UNION ALL select 'b',30,'2007-1-1'
UNION ALL select 'b',20,'2007-1-2'
UNION ALL select 'c',50,'2007-1-3'
UNION ALL select 'c',100,'2007-1-4'create function f_GetB(@a nvarchar(50),@c datetime)
returns int
begin
return(SELECT max(B) from TEST where A=@a and C=@c)
endSELECT a,dbo.f_GETB(a,MAx(c)),max(C) from test group by A
DROP TABLE TEST------------------------------------------
a 100 2007-01-01 00:00:00.000
b 20 2007-01-02 00:00:00.000
c 100 2007-01-04 00:00:00.000
========================是错误的!hongqi162正解!!!