id scale stockid
-------------------------------------------
1 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
2 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
3 18509 7704 33211BE29C5E4C075573E06D7FF5776C
4 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
5 12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
6 12506 7704 33211BE29C5E4C075573E06D7FF5776C按id分组取最大scale的stockid...
-------------------------------------------
1 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
2 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
3 18509 7704 33211BE29C5E4C075573E06D7FF5776C
4 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
5 12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
6 12506 7704 33211BE29C5E4C075573E06D7FF5776C按id分组取最大scale的stockid...
id scale stockid
---------------------------------------------
1 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
2 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
3 18509 7704 33211BE29C5E4C075573E06D7FF5776C
4 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
5 12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
6 12506 7704 33211BE29C5E4C075573E06D7FF5776C
where scale=(select max(scale) form tb where id=t.id)
where not exists(select 1 form tb where id=t.id and scale>t.scale)
按id分组取最大scale的stockid...
2 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1 AAAPzfAAFAAABL+AAB
3 18509 7704 33211BE29C5E4C075573E06D7FF5776C AAAPzfAAFAAABL+AAC
4 12506 7704 33211BE29C5E4C075573E06D7FF5776C AAAPzfAAFAAABL+AAD
5 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1 AAAPzfAAFAAABL+AAE
6 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C AAAPzfAAFAAABL+AAF
如果数据是这样的话你看结果对不对....
left join (select id,max(scale) as scale from tablename group by id) b on a.id=b.id and a.scale=b.scale
你是不是把需求写错了?是按scale分组取最id的stockid吧?
select * from tablename a
left join (select scale,max(id) as id from tablename group by scale) b on a.id=b.id and a.scale=b.scale
是按id分组取最大scale的stockid
id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
18509 7704 33211BE29C5E4C075573E06D7FF5776C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
12506 7704 33211BE29C5E4C075573E06D7FF5776C
要求就是取scale最大的那条
结果:
1 18509 7704 33211BE29C5E4C075573E06D7FF5776C 7704 18509
2 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C 7704 18509
3 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1 19999 12506
4 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1 0 18509
5 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
6 12506 7704 33211BE29C5E4C075573E06D7FF5776C
id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
18509 7704 33211BE29C5E4C075573E06D7FF5776C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
12506 7704 33211BE29C5E4C075573E06D7FF5776C结果:
id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
这样的?
select a.id,a.scale,max(stockid) as stockid
from tablename a
left join (select id,max(scale) as scale from tablename group by id) b on a.id=b.id and a.scale=b.scale
group by a.id,a.scale
能帖出来么!
我在oracle下运行结果:1 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
2 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
3 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1
4 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
(
seq int identity(1,1),
id int,
scale int,
stockid varchar(100)
)insert into table1
select 18509,7704,'33211BE29C5E4C075573E06D7FF5776C'
union select 18509,7704,'F9320BE29C5E4C07A6573E0D6D7FF56C'
union select 12506,19999,'C71D4E4205F24CFCB8EDC7D4460B61F1'
union select 18509,0,'C71D4E4205F24CFCB8EDC7D4460B61F1'
union select 12506,7704,'F9320BE29C5E4C07A6573E0D6D7FF56C'
union select 12506, 7704,'33211BE29C5E4C075573E06D7FF5776C' select * from table1
---------------------------------
seq id scale stockid
1 12506 7704 33211BE29C5E4C075573E06D7FF5776C
2 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
3 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1
4 18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
5 18509 7704 33211BE29C5E4C075573E06D7FF5776C
6 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
select a.scale,max(stockid) as stockid
from table1 a
right join (select id,max(scale) as scale from table1 group by id) b on a.id=b.id and a.scale=b.scale
group by a.scale
---------------------------------
scale stockid
7704 F9320BE29C5E4C07A6573E0D6D7FF56C
19999 C71D4E4205F24CFCB8EDC7D4460B61F1
select a.id,a.scale,max(stockid) as stockid
from table1 a
right join (select id,max(scale) as scale from table1 group by id) b on a.id=b.id and a.scale=b.scale
group by a.id,a.scale
---------------------------------
id scale stockid
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1
select a.id,a.scale,min(stockid) as stockid
from table1 a
right join (select id,min(scale) as scale from table1 group by id) b on a.id=b.id and a.scale=b.scale
group by a.id,a.scale
---------------------------------
id scale stockid
18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
12506 7704 33211BE29C5E4C075573E06D7FF5776C
哪里错了?
2 18509 0 Z71D4E4205F24CFCB8EDC7D4460B61F1 AAAOVmAAIAAAW8FAAB
3 18509 7704 33211BE29C5E4C075573E06D7FF5776C AAAOVmAAIAAAW8FAAC
4 12506 7704 z3211BE29C5E4C075573E06D7FF5776C AAAOVmAAIAAAW8FAAD
5 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1 AAAOVmAAIAAAW8FAAE
6 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C AAAOVmAAIAAAW8FAAF执行:select * from testaa where stockid IN (select max(stockid) from testaa GROUP BY id)结果:
1 18509 0 Z71D4E4205F24CFCB8EDC7D4460B61F1
2 12506 7704 z3211BE29C5E4C075573E06D7FF5776C
select t.* from tb t where stock=(select max(scale) from tb) group by t.id