22,207
社区成员
发帖
与我相关
我的任务
分享
--你如果先执行删除,是看不到那些数据了
SELECT * FROM t_da_saleman_daysumb
WHERE EXISTS(
SELECT 1 FROM t_da_saleman_daysumb T
WHERE t_da_saleman_daysumb.col002=T.col002
AND t_da_saleman_daysumb.col003=T.col003
AND t_da_saleman_daysumb.col004=T.col004
AND t_da_saleman_daysumb.col005=T.col005
AND t_da_saleman_daysumb.col001>T.col001)
--你如果先执行删除,是看不到那些数据了
SELECT * FROM t_da_saleman_daysumb
WHERE EXISTS(
SELECT 1 FROM t_da_saleman_daysumb T
WHERE t_da_saleman_daysumb.col002=T.col002
AND t_da_saleman_daysumb.col003=T.col003
AND t_da_saleman_daysumb.col004=T.col004
AND t_da_saleman_daysumb.col005=T.col005
AND t_da_saleman_daysumb.col001>T.col001)
delete t_da_saleman_daysumb
from t_da_saleman_daysumb a
left join (select min(col001) col01 from t_da_saleman_daysumb group by col002,col003,col004,col005) t
on a.col001=t.col001
where t.col001 is null
select min(col001) col001
into #t
from t_da_saleman_daysumb
group by col002,col003,col004,col005;
deletee from t_da_saleman_daysumb
where col001 not in (select col001 from #t)
DELETE t_da_saleman_daysumb
WHERE EXISTS(
SELECT 1 FROM t_da_saleman_daysumb T
WHERE t_da_saleman_daysumb.col002=T.col002
AND t_da_saleman_daysumb.col003=T.col003
AND t_da_saleman_daysumb.col004=T.col004
AND t_da_saleman_daysumb.col005=T.col005
AND t_da_saleman_daysumb.col001>T.col001)
try;WITH CTE AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY col002,col003,col004,col005 ORDER BY col001)RN
FROM t_da_saleman_daysumb
)
DELETE CTE
WHERE RN>1