求助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重复的，只保留一次记录
最佳答案
1
如图，先按重复字段分组得出一个结果集，再取重复ID中的第一条，如果要取最后一条则在里面加个倒序即可
收获园豆：20
按着套了下感觉有些懵了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
@瘦子梅西尔: 帮你调整并格式化了一下，我这边没法执行，你执行下看有没有什么疏漏之处
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;
@默卿: 感谢麻烦了还是有些问题，会出现重复的
@瘦子梅西尔: 刚刚解决的是ScanId相同，Id不同的数据，解决了这种数据以后出现全部相同的数据的话，在最后查询出来的结果集前面加个DISTINCT即可。即SELECT 后面加个DISTINCT。
@默卿: 十分感谢实现了，我在研究研究
其他回答(1)
0
看到你这个SQL语句就忍不住了，既然是比对年和月，为什么不写成between 2020-05-01 0:00:00 and 2020-05-31 :23:59:59呢？ 这样必须给CreateTime字段加索引的，就走索引了啊
大神们谢谢了，现在的sql非常的慢，准备优化下sql把一部分逻辑处理放到程序里感觉这样
