记录有自增列字段,如何找到最高产量对应的自增列的值呢?
比如字段为:
autoid是自增列autoid,月份,日期,机台号,班次号,机台产量,分配比例1,200901,02,a,白班,200,0.6
2,200901,02,a,白班,200,0.43,200901,03,a,中班,200,0.9
4,200901,03,a,中班,200,0.15,200901,04,c,白班,200,0.5
6,200901,04,c,白班,200,0.2
7,200901,04,c,白班,200,0.38,200901,05,d,白班,200,0.7
9,200901,05,d,白班,200,0.3我想求出,每个班次中,分配比例最高的记录对应的自增列 autoid,因为这条记录是机长的记录,因为机长的分配比例最高
比如字段为:
autoid是自增列autoid,月份,日期,机台号,班次号,机台产量,分配比例1,200901,02,a,白班,200,0.6
2,200901,02,a,白班,200,0.43,200901,03,a,中班,200,0.9
4,200901,03,a,中班,200,0.15,200901,04,c,白班,200,0.5
6,200901,04,c,白班,200,0.2
7,200901,04,c,白班,200,0.38,200901,05,d,白班,200,0.7
9,200901,05,d,白班,200,0.3我想求出,每个班次中,分配比例最高的记录对应的自增列 autoid,因为这条记录是机长的记录,因为机长的分配比例最高
where not exists(select 1 from tb where 班次号=t.班次号 and 分配比例>t.分配比例)
from tb
order by 分配比例 desc
autoid
from
tb t
where
not exists(select 1 from tb where 班次号=t.班次号 and 分配比例>t.分配比例)
select autoid
from tb t
where autoid=(select top 1 autoid from tb where 班次号=t.班次号 order by 分配比例 desc)
select autoid from @tb a
where not exists (select 1 from @tb where a.班次号=b.班次号 and 分配比例>a.分配比例
where not exists (select 1 from @tb where a.班次号=b.班次号 and 分配比例>a.分配比例)
where not exists (select 1 from @tb where a.班次号=b.班次号 and 分配比例>a.分配比例)