删除数据表中多余重复列

最新推荐文章于 2020-06-08 11:19:55 发布

luobosibier

最新推荐文章于 2020-06-08 11:19:55 发布

阅读量820

点赞数

分类专栏： MS Sql Server工作笔记文章标签： delete sql

本文链接：https://blog.csdn.net/luobosibier/article/details/6635442

版权

MS Sql Server工作笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

如下所示，同一个DWBH有2条或多条数据，现在要求同一个DWBH只保留最近一条数据(CJSJ最大的那条)，设表名为A。

XCZFBH                                             DWBH                                               CJSJ
-------------------------------------------------- -------------------------------------------------- -----------------------
32058320112388                                     320583038813                                       2011-06-29 15:38:06.960
32058320112389                                     320583038813                                       2011-06-29 15:40:58.980
34582011052302                                     320583038458                                       2011-05-23 14:55:00.000
27382011062203                                     320583038458                                       2011-06-22 10:26:00.000
84582011070402                                     320583038458                                       2011-07-04 10:31:00.000
32058320112832                                     320583038458                                       2011-07-12 10:05:00.000
32058320112848                                     320583038458                                       2011-07-22 10:22:00.000
70142011061302                                     320583037014                                       2011-06-13 14:58:00.000
70142011062404                                     320583037014                                       2011-06-24 14:10:00.000
70142011070406                                     320583037014                                       2011-07-04 14:07:00.000
32058320112469                                     320583037014                                       2011-07-22 10:43:00.000

_________________________________________________________________________________________________________

sql语句如下：

delete from A where CJSJ not in (select Max(CJSJ) from A where DWBH in (select DWBH from A group by DWBH having COUNT(DWBH)>1) group by DWBH ) and DWBH in (select DWBH from A group by DWBH having COUNT(DWBH)>1);

______________________________________________________________________________________________________________