表table1 如下
F1 F2 F3 F4
A 1 3 2
A 2 3 1
A 3 2 3
B 1 4 2
B 2 4 6
B 3 2 3
C 1 2 3
C 3 6 3
C 4 7 9
想要得到的记录如下
F1 F2 F3 F4
A 3 2 3
B 3 2 3
C 4 7 9就是根据F1,取得最大的F2,并直接带出F3,F4;不想用下面的方法,因为数据量实在太大,如果用下面方法,速度会很慢,请高手指点更好的办法,先谢过!
select t1.*
from Table1 t1
inner join (select f1,max(f2) as f2 from Table1 group by f1) t2
on t1.F1 = t2.f1 and t1.f2 = t2.f2
F1 F2 F3 F4
A 1 3 2
A 2 3 1
A 3 2 3
B 1 4 2
B 2 4 6
B 3 2 3
C 1 2 3
C 3 6 3
C 4 7 9
想要得到的记录如下
F1 F2 F3 F4
A 3 2 3
B 3 2 3
C 4 7 9就是根据F1,取得最大的F2,并直接带出F3,F4;不想用下面的方法,因为数据量实在太大,如果用下面方法,速度会很慢,请高手指点更好的办法,先谢过!
select t1.*
from Table1 t1
inner join (select f1,max(f2) as f2 from Table1 group by f1) t2
on t1.F1 = t2.f1 and t1.f2 = t2.f2
if object_id('tempdb.dbo.#') is not null drop table #
create table #(F1 varchar(8), F2 int, F3 int, F4 int)
insert into #
select 'A', 1, 3, 2 union all
select 'A', 2, 3, 1 union all
select 'A', 3, 2, 3 union all
select 'B', 1, 4, 2 union all
select 'B', 2, 4, 6 union all
select 'B', 3, 2, 3 union all
select 'C', 1, 2, 3 union all
select 'C', 3, 6, 3 union all
select 'C', 4, 7, 9select * from # as t where not exists (select 1 from # where F1=t.F1 and F2>t.F2)/*
F1 F2 F3 F4
-------- ----------- ----------- -----------
A 3 2 3
B 3 2 3
C 4 7 9
*/
from Table1 t1
inner join (select f1,max(f2) as f2 from Table1 group by f1) t2
on t1.F1 = t2.f1 and t1.f2 = t2.f2
这方法加上index应该算是相当不错的。
select F1,F2,F3,F4
from
(
select rn=ROW_NUMBER() over(partition by F1 order by F2 desc),*
from table1
) T
where rn=1
select t2.FDate as 日期,t2.FBillNo as 单据编号,t1.FSNCode as ' + char(39) + '串号' + char(39) + @CC +',' +
' t6.FName as 机型名称,t10.FName as 发货仓库,t4.FName as 部门,convert(numeric(15,2),t5.FConsignPrice) as 价格,
isnull(t7.f_112,0) as 摩托管控,convert(bit,(Case isnull(t8.fsncode,0) when t8.fsncode then 1 else 0 end)) as 已销售,
t8.FDate as 消化日期,t14.FName as 促消员,case when isnull(m1.FInterID,0) = 0 then '''' else ''Y'' end as 已退货
from com_inventory_snbill t1
inner join ' + @DB + '..ICStockBill t2 on t1.FCom_Interid=t2.FInterID and (t1.finterid =(select max(finterid) from com_inventory_snbill m where m.fsncode =t1.fsncode and fscreenid= '+'''020200'''+' ))
inner join ' + @DB + '..t_Department t4 on t2.fdeptid=t4.fitemid
inner join ' + @DB + '..ICStockBillEntry t5 on t1.FCom_Interid=t5.FInterID and t1.FCom_EntryId=t5.FEntryid
inner join ' + @DB + '..t_ICItem t6 on t1.fitemid=t6.fitemid
inner join '+@DB+'..t_Organization t7 on t2.FSupplyID=t7.fitemid
inner join '+@DB+'..t_Stock t10 on t1.FStockIDOut=t10.fitemid
left join com_snbill_backcard t8 on t8.fitemid=t1.fitemid and t8.fsncode=t1.fsncode
left join '+@DB+'..t_Item_3029 t14 on t8.FSallerID=t14.FItemID
inner join ' + @DB + '..t_Organization t3 on (Case isnull(t7.f_113,0) when 0 then t7.fitemid else t7.f_113 end )=t3.fitemid
left join com_inventory_snbill m1 on t1.FSNCode = m1.FSNCode and m1.FSCreenID = '+'''020400'''+' and m1.FDate >= t1.FDate and m1.FInterID > t1.FInterID
where t1.fscreenid= '+'''020200'''+' and '+@Fliter+' '
exec (@aa)