首页新闻找找看学习计划

求助sql大佬,去重复字段

0
悬赏园豆:20 [已解决问题] 解决于 2020-05-19 09:42

sql

select CONVERT(varchar(10) , a.CreateTime, 120 ) ScanData,a.Id,b.Name,c.Remark from
Tb_IMAGESUSPICOUS a left join Tb_BUSSHAPECONFIG b on cast(a.SuspicousType as int)=b.Id
left join Tb_SHAPERECOGNITIONDIC c on a.Shape=c.ClassesLineNo
where YEAR(a.CreateTime)='2020'
and MONTH(a.CreateTime)='05'

查询结果

需要去除同一ScanId下的Remark重复的,只保留一次记录

sql
瘦子梅西尔的主页 瘦子梅西尔 | 初学一级 | 园豆:150
提问于:2020-05-18 10:35
< >
分享
最佳答案
1

如图,先按重复字段分组得出一个结果集,再取重复ID中的第一条,如果要取最后一条则在里面加个倒序即可

收获园豆:20
默卿 | 老鸟四级 |园豆:4400 | 2020-05-18 11:45

按着套了下感觉有些懵了0.0,查询结果有些问题大神帮忙看下吧
select ScanData,
(select top(1) Id from
(select d.ScanId from
Tb_IMAGESUSPICOUS a left join Tb_BUSSHAPECONFIG b on cast(a.SuspicousType as int)=b.Id
left join Tb_SHAPERECOGNITIONDIC c on a.Shape=c.ClassesLineNo
left join Tb_IMAGE d on d.Id=a.ImageId
where YEAR(a.CreateTime)='2020'
and MONTH(a.CreateTime)='05')z

where z.ScanId=T.Id)Id,T.Name,T.ScanId,T.Remark
FROM(select CONVERT(varchar(10) , a.CreateTime, 120 ) ScanData,a.Id,b.Name,c.Remark,d.ScanId from
Tb_IMAGESUSPICOUS a left join Tb_BUSSHAPECONFIG b on cast(a.SuspicousType as int)=b.Id
left join Tb_SHAPERECOGNITIONDIC c on a.Shape=c.ClassesLineNo
left join Tb_IMAGE d on d.Id=a.ImageId
where YEAR(a.CreateTime)='2020'
and MONTH(a.CreateTime)='05'
Group by a.CreateTime,b.Name,c.Remark,d.ScanId,a.Id)T

瘦子梅西尔 | 园豆:150 (初学一级) | 2020-05-18 16:15

@瘦子梅西尔: 帮你调整并格式化了一下,我这边没法执行,你执行下看有没有什么疏漏之处
SELECT ScanData ,
(SELECT TOP 1 d.Id
FROM Tb_IMAGESUSPICOUS a
LEFT JOIN Tb_BUSSHAPECONFIG b ON CAST(a.SuspicousType AS INT) = b.Id
LEFT JOIN Tb_SHAPERECOGNITIONDIC c ON a.Shape = c.ClassesLineNo
LEFT JOIN Tb_IMAGE d ON d.Id = a.ImageId
WHERE YEAR(a.CreateTime) = '2020'
AND MONTH(a.CreateTime) = '05'
AND ScanId=T.ScanId
) Id ,
T.Name ,
T.ScanId ,
T.Remark
FROM ( SELECT CONVERT(VARCHAR(10), a.CreateTime, 120) ScanData ,
b.Name ,
c.Remark ,
d.ScanId
FROM Tb_IMAGESUSPICOUS a
LEFT JOIN Tb_BUSSHAPECONFIG b ON CAST(a.SuspicousType AS INT) = b.Id
LEFT JOIN Tb_SHAPERECOGNITIONDIC c ON a.Shape = c.ClassesLineNo
LEFT JOIN Tb_IMAGE d ON d.Id = a.ImageId
WHERE YEAR(a.CreateTime) = '2020'
AND MONTH(a.CreateTime) = '05'
GROUP BY a.CreateTime ,
b.Name ,
c.Remark ,
d.ScanId
) T;

默卿 | 园豆:4400 (老鸟四级) | 2020-05-18 16:49

@默卿: 感谢麻烦了还是有些问题,会出现重复的

瘦子梅西尔 | 园豆:150 (初学一级) | 2020-05-18 17:14

@瘦子梅西尔: 刚刚解决的是ScanId相同,Id不同的数据,解决了这种数据以后出现全部相同的数据的话,在最后查询出来的结果集前面加个DISTINCT即可。即SELECT 后面加个DISTINCT。

默卿 | 园豆:4400 (老鸟四级) | 2020-05-18 18:15

@默卿: 十分感谢实现了,我在研究研究

瘦子梅西尔 | 园豆:150 (初学一级) | 2020-05-19 09:41
其他回答(1)
0

看到你这个SQL语句就忍不住了,既然是比对年和月,为什么不写成between 2020-05-01 0:00:00 and 2020-05-31 :23:59:59呢? 这样必须给CreateTime字段加索引的,就走索引了啊

咖啡不会醉 | 园豆:209 (菜鸟二级) | 2020-05-19 11:05

大神们谢谢了,现在的sql非常的慢,准备优化下sql把一部分逻辑处理放到程序里感觉这样

支持(0) 反对(0) 瘦子梅西尔 | 园豆:150 (初学一级) | 2020-05-26 14:53
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册