有一个表,表中有三个字段a,b,c 我想以a字段分组,求当组中b为最大值时c的值!select a,(select max(b) from tableA where a=TA.a),c from table A TA
select a,(select max(b) from tableA where a=TA.a) maxB,c from table A TA group by a,c
如下即可: select tb1.a, tb1.b, c from 表名 as tb1 Join (select a, max(b) from 表名 group by a) as tb2 ON tb2.a=tb1.a
一下子真写不出来,你先这样实现吧: --测试环境 set nocount on create table T(a varchar(10),b int,c varchar(10)) insert into T select 'a',1,'dsf' union select 'b',3,'dfds' union select 'e',4,'adf' union select 'x',9,'fsd' union select 'a',56,'fd' union select 'a',7,'g' union select 'b',10,'fd' select * from T declare @T table (a varchar(10),b int) insert into @T select distinct a,max(b) b from T group by a select Tb.a,Tb.b,Tb.c from @T Ta,T Tb where Tb.a=Ta.a and Tb.b=Ta.bgroup by Tb.a,Tb.b,Tb.c drop table T--结果 a b c ---------- ----------- ---------- a 1 dsf a 7 g a 56 fd b 3 dfds b 10 fd e 4 adf x 9 fsda b c ---------- ----------- ---------- a 56 fd b 10 fd e 4 adf x 9 fsd
简单些: select a1.a,a1.b,a1.c from t a1,(select a,max(b) b from t group by a) a2 where a1.b=a2.b 结果: +------+------+------+ | a | b | c | +------+------+------+ | e | 4 | adf | | x | 9 | fsd | | a | 56 | fd | | b | 10 | fd | +------+------+------+
select c from ( select max(b) as Bmax from ( select b,c from tableName group by a ) ) BBB,tableName AAA where AAA.b=BBB.Bmax
上面是在mysql中的结果,在sqlserver中为 a 56 fd b 10 fd e 4 adf x 9 fsd
select g.a,g.c from (select a,max(b) b from 表名 group a) f,表名 g where f.a=g.a and f.b= g.b
我在delphi6中的SQL Explorer中运行这条语句: select g.a,g.c from (select a,max(b) b from 表名 group a) f,表名 g where f.a=g.a and f.b= g.b 报错: 没有为第二列(属于f)指定列。这个问题是怎么回事啊?
我想以a字段分组,求当组中b为最大值时c的值!select a,(select max(b) from tableA where a=TA.a),c from table A TA
group by a,c
select tb1.a, tb1.b, c from 表名 as tb1
Join (select a, max(b) from 表名 group by a) as tb2 ON tb2.a=tb1.a
--测试环境
set nocount on
create table T(a varchar(10),b int,c varchar(10))
insert into T
select 'a',1,'dsf'
union
select 'b',3,'dfds'
union
select 'e',4,'adf'
union
select 'x',9,'fsd'
union
select 'a',56,'fd'
union
select 'a',7,'g'
union
select 'b',10,'fd'
select * from T
declare @T table (a varchar(10),b int)
insert into @T
select distinct a,max(b) b from T
group by a
select Tb.a,Tb.b,Tb.c from @T Ta,T Tb where Tb.a=Ta.a and Tb.b=Ta.bgroup by Tb.a,Tb.b,Tb.c
drop table T--结果
a b c
---------- ----------- ----------
a 1 dsf
a 7 g
a 56 fd
b 3 dfds
b 10 fd
e 4 adf
x 9 fsda b c
---------- ----------- ----------
a 56 fd
b 10 fd
e 4 adf
x 9 fsd
select a1.a,a1.b,a1.c from t a1,(select a,max(b) b from t group by a) a2
where a1.b=a2.b
结果:
+------+------+------+
| a | b | c |
+------+------+------+
| e | 4 | adf |
| x | 9 | fsd |
| a | 56 | fd |
| b | 10 | fd |
+------+------+------+
from
( select max(b) as Bmax
from
( select b,c
from tableName
group by a
)
) BBB,tableName AAA
where AAA.b=BBB.Bmax
a 56 fd
b 10 fd
e 4 adf
x 9 fsd
where f.a=g.a and f.b= g.b
select g.a,g.c from (select a,max(b) b from 表名 group a) f,表名 g
where f.a=g.a and f.b= g.b
报错:
没有为第二列(属于f)指定列。这个问题是怎么回事啊?
报错是因为我少写了东西