sql结果集去重
0
悬赏园豆：20
[已解决问题]
解决于 2014-09-18 14:22
我想将查询的结果中，Barcode字段相同的记录项，只取Id最大的一条，其他的保留。
问题补充：
SELECT ContractID as 工单号,BarCode,Pmax,PTitle as 功率档,PScope,Ipm,ITitle as 电流档,IScope,Spec as 规格 ,ProductLevel as 产品等级,BatteryLevel as 电池片等级,BadReason as FQC不良 ,(select top 1 USERNAME from LoginUser where USERID=T_FQC.Employee) as 员工 ,Remark 备注 ,CONVERT(varchar(100),ScanDate,120) as 时间 FROM T_FQC where Employee='9000778' and CONVERT(varchar(100),DateTime,120)='2014-03-12 19:07:16' and Id=(select top 1 Id from T_FQC b where b.BarCode=T_FQC.BarCode and b.Employee='9000778' and CONVERT(varchar(100),b.DateTime,120)='2014-03-12 19:07:16' order by b.Id desc) order by Id desc
这种写法对CPU消耗过大，想换个写法
最佳答案
0
SELECT ContractID as 工单号,BarCode,Pmax,PTitle as 功率档,PScope,Ipm,ITitle as 电流档,IScope,Spec as 规格 ,ProductLevel as 产品等级,BatteryLevel as 电池片等级,BadReason as FQC不良 ,(select top 1 USERNAME from LoginUser where USERID=T_FQC.Employee) as 员工 ,Remark 备注 ,CONVERT(varchar(100),ScanDate,120) as 时间 FROM T_FQC inner join (select barcode, max(id) as id from t_fqc) t on t_fqc.id=t.id where Employee='9000778' and CONVERT(varchar(100),DateTime,120)='2014-03-12 19:07:16' order by Id desc
收获园豆：10
其他回答(6)
0
应该要用Group By然后在分组内Max(ID)
PS:被你的中文字段名亮瞎了。。。
0
Id=(select top 1 Id from T_FQC b where b.BarCode=T_FQC.BarCode and b.Employee='9000778' and CONVERT(varchar(100),b.DateTime,120)='2014-03-12 19:07:16' order by b.Id desc)
将其中的top改写成max可以有效降低cpu消耗
0
select max(id) group by id,barcode.
0
select * from tb_A where id in( select max(id) from tb_A group by col
)
0
--drop table #temp select * from [test] ;with res as(select max(id)id,code from test group by code)select * into #temp from res select b.* from #temp a inner join test b on a.id=b.id and a.code=b.code
收获园豆：10
0
select * from (
select row_number() over( partition by BarCode order by ID desc ) __RN, * from T_FQC
)
where __RN = 1
