首页新闻找找看学习计划

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消耗过大,想换个写法

FreeSaber的主页 FreeSaber | 初学一级 | 园豆:85
提问于:2014-09-11 11:45
< >
分享
最佳答案
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
519740105 | 大侠五级 |园豆:5810 | 2014-09-11 13:48
其他回答(6)
0

应该要用Group By然后在分组内Max(ID)

XiaoFaye | 园豆:3082 (老鸟四级) | 2014-09-11 11:46

PS:被你的中文字段名亮瞎了。。。

支持(0) 反对(0) XiaoFaye | 园豆:3082 (老鸟四级) | 2014-09-11 11:48
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消耗

FreeSaber | 园豆:85 (初学一级) | 2014-09-11 12:23
0

select max(id) group by id,barcode.

Cat Qi | 园豆:761 (小虾三级) | 2014-09-11 15:40
0

select * from tb_A where id in(   select max(id) from  tb_A group by col
)

happydaily | 园豆:674 (小虾三级) | 2014-09-12 08:35
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
CTRA王大大 | 园豆:30 (初学一级) | 2014-09-12 12:03
0

select * from (

select row_number() over( partition by BarCode order by ID desc ) __RN, * from T_FQC

)

where __RN = 1

爱上老鼠的pug | 园豆:60 (初学一级) | 2014-09-14 04:14
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册