精华内容
下载资源
问答
  • sqlserver去重
    2020-06-15 10:04:54

    SELECT * FROM (
    select *,ROW_NUMBER() over(partition by (first_leve_kind+second_leve_kind+system_name+document_number+document_number+clause_number) order by id desc) AS rn from t_dic_applicable_provisions_problem 
    ) AS u WHERE u.rn=1 and u.app_type=4

    更多相关内容
  • SQL Server 去重

    2013-07-21 10:16:37
    关于SQL Server中的去重问题,内含详细测试数据
  • SQLSERVER去重语句.docx

    2022-03-12 14:30:27
    SQLSERVER去重语句.
  • SQLSERVER去重

    2022-04-20 10:45:39
    SQLSERVER去重 1.根据单字段,查询表中该字段重复出现记录: SELECT * FROM Table WHERE Parameter IN (SELECT Parameter FROM Table GROUP BY Parameter HAVING COUNT(Parameter ) > 1) 分析: SELECT ...

    SQLSERVER去重

    1.根据单字段,查询表中该字段重复出现记录:

    SELECT * FROM Table
    WHERE Parameter IN
    (SELECT Parameter FROM Table
    GROUP BY Parameter HAVING COUNT(Parameter ) > 1)

    分析:
    SELECT Parameter FROM Table
    GROUP BY Parameter HAVING COUNT(Parameter ) > 1
    根据字段分组,查询出表中出现重复(字段数量>1)的字段集

    -----------------------------华丽的分割线---------------------------------

    2.根据单字段,删除表中该字段重复出现记录,保留最小主键ID

    DELETE FROM Table
    WHERE Parameter IN
    (SELECT Parameter FROM Table
    GROUP BY Parameter HAVING COUNT(Parameter) > 1)
    AND ID NOT IN
    (SELECT MIN(ID) FROM Table
    GROUP BY Parameter HAVING COUNT(Parameter)>1)


    -----------------------------华丽的分割线---------------------------------

    3.根据多个字段查询表中重复的记录

    SELECT * FROM Table a
    WHERE EXISTS
    (SELECT P

    展开全文
  • SQLSERVER 去重

    2020-12-28 22:24:03
    --无唯一列(或主键)去重,只保留一行,方法3:将重复以及不重复数据去重插入新表,删除旧表相应数据,再把新表数据插入旧表 SELECT DISTINCT * INTO students2 FROM dbo.students WHERE stu_birthday>='2002-09-01...

    --添加测试数据

    INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('宋江', '1', '13142224589', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('宋江', '1', '13142224589', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('卢俊义', '1', '18942224222', '2000-04-11');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('吴用', '0', '13942224511', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('公孙胜', '1', '13442228589', '1902-05-09');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('关胜', '1', '17642224566', '2002-09-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('林冲', '1', '18642224989', '2011-08-07');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('关胜', '1', '17642224566', '2002-09-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('林冲', '1', '18642224989', '2011-08-07');GO

    --查询重复数据

    SELECT S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday FROM dbo.students S GROUP BY S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday HAVING COUNT(1)>1

    --根据某个字段去重

    SELECT stu_name,s.stu_phone FROM ( SELECT stu_name,stu_phone ,ROW_NUMBER() OVER (PARTITION BY stu_name ORDER BY stu_phone) rn FROM dbo.students )AS s WHERE rn=1

    --查询去重,写法1

    SELECT DISTINCT stu_name,stu_gender,stu_phone,stu_birthday FROMdbo.students--查询去重,写法2

    SELECT stu_id, stu_name,stu_gender,stu_phone,stu_birthday FROM dbo.students WHERE stu_id IN(SELECT MAX(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday)--删除重复的数据,方法1

    DELETE FROM dbo.students WHERE stu_id NOT IN(SELECT MIN(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday)--删除重复的数据,方法2

    DELETE FROM dbo.students WHERE NOT EXISTS(SELECT 1 FROM (SELECT MIN(S.stu_id) AS id FROM dbo.students S GROUP BY S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday) AS G WHERE students.stu_id=g.id)--删除重复的数据,方法3

    CREATE TABLE #TBL(ID INT PRIMARY KEY);INSERT INTO #TBL(ID) SELECT MIN(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday;DELETE FROM dbo.students WHERE stu_id NOT IN(SELECT T.ID FROM#TBL T)DROP TABLE#TBL--删除重复的数据,方法4

    DELETE dbo.students FROM dbo.students ASaaLEFT JOIN(SELECT MIN(stu_id) AS id FROMdbo.studentsGROUP BYstu_name,stu_gender,stu_phone,stu_birthday

    )ASbbON aa.stu_id = bb.id WHERE bb.id is null

    --无唯一列(或主键)去重--ALTER TABLE dbo.students DROP CONSTRAINT PK_students

    ALTER TABLE dbo.students drop column stu_id;--创建数据,删除stu_id--无唯一列(或主键)去重,只保留一行,方法1:查询去重后的数据后放到另一个表,删除原表,然后重命名--注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

    SELECT DISTINCT * INTO dbo.students2 FROMdbo.students;DROP TABLEdbo.students;EXEC sp_rename 'students2', 'students';--无唯一列(或主键)去重,只保留一行,方法2:增加唯一列

    ALTER TABLE dbo.students ADD stu_id INT IDENTITY(1,1);DELETE FROM dbo.students WHERE stu_id NOT IN(selectstu_idFROM (SELECT stu_id,ROW_NUMBER() OVER(PARTITION BY stu_name,stu_gender,stu_phone,stu_birthday ORDER BY stu_name) ASrnFROMdbo.students ) sWHERE rn=1);--无唯一列(或主键)去重,只保留一行,方法3:将重复以及不重复数据去重插入新表,删除旧表相应数据,再把新表数据插入旧表

    SELECT DISTINCT * INTO students2 FROM dbo.students WHERE stu_birthday>='2002-09-01';--条件

    DELETE FROM dbo.students WHERE stu_birthday>='2002-09-01';INSERT INTO dbo.students(stu_name, stu_gender, stu_phone, stu_birthday) SELECT * FROMdbo.students2;DROP TABLE dbo.students2

    展开全文
  • SqlServer去重

    2021-10-28 23:22:39
    --无唯一列(或主键)去重,只保留一行,方法3:将重复以及不重复数据去重插入新表,删除旧表相应数据,再把新表数据插入旧表 SELECT DISTINCT * INTO students2 FROM dbo.students WHERE stu_birthday>='2002-09-01';...

    --添加测试数据

    INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('宋江', '1', '13142224589', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('宋江', '1', '13142224589', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('卢俊义', '1', '18942224222', '2000-04-11');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('吴用', '0', '13942224511', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('公孙胜', '1', '13442228589', '1902-05-09');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('关胜', '1', '17642224566', '2002-09-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('林冲', '1', '18642224989', '2011-08-07');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('关胜', '1', '17642224566', '2002-09-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('林冲', '1', '18642224989', '2011-08-07');GO

    --查询重复数据

    SELECT S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday FROM dbo.students S GROUP BY S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday HAVING COUNT(1)>1

    --根据某个字段去重

    SELECT stu_name,s.stu_phone FROM ( SELECT stu_name,stu_phone ,ROW_NUMBER() OVER (PARTITION BY stu_name ORDER BY stu_phone) rn FROM dbo.students )AS s WHERE rn=1

    --查询去重,写法1

    SELECT DISTINCT stu_name,stu_gender,stu_phone,stu_birthday FROMdbo.students--查询去重,写法2

    SELECT stu_id, stu_name,stu_gender,stu_phone,stu_birthday FROM dbo.students WHERE stu_id IN(SELECT MAX(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday)--删除重复的数据,方法1

    DELETE FROM dbo.students WHERE stu_id NOT IN(SELECT MIN(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday)--删除重复的数据,方法2

    DELETE FROM dbo.students WHERE NOT EXISTS(SELECT 1 FROM (SELECT MIN(S.stu_id) AS id FROM dbo.students S GROUP BY S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday) AS G WHERE students.stu_id=g.id)--删除重复的数据,方法3

    CREATE TABLE #TBL(ID INT PRIMARY KEY);INSERT INTO #TBL(ID) SELECT MIN(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday;DELETE FROM dbo.students WHERE stu_id NOT IN(SELECT T.ID FROM#TBL T)DROP TABLE#TBL--删除重复的数据,方法4

    DELETE dbo.students FROM dbo.students ASaaLEFT JOIN(SELECT MIN(stu_id) AS id FROMdbo.studentsGROUP BYstu_name,stu_gender,stu_phone,stu_birthday

    )ASbbON aa.stu_id = bb.id WHERE bb.id is null

    --无唯一列(或主键)去重--ALTER TABLE dbo.students DROP CONSTRAINT PK_students

    ALTER TABLE dbo.students drop column stu_id;--创建数据,删除stu_id--无唯一列(或主键)去重,只保留一行,方法1:查询去重后的数据后放到另一个表,删除原表,然后重命名--注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

    SELECT DISTINCT * INTO dbo.students2 FROMdbo.students;DROP TABLEdbo.students;EXEC sp_rename 'students2', 'students';--无唯一列(或主键)去重,只保留一行,方法2:增加唯一列

    ALTER TABLE dbo.students ADD stu_id INT IDENTITY(1,1);DELETE FROM dbo.students WHERE stu_id NOT IN(selectstu_idFROM (SELECT stu_id,ROW_NUMBER() OVER(PARTITION BY stu_name,stu_gender,stu_phone,stu_birthday ORDER BY stu_name) ASrnFROMdbo.students ) sWHERE rn=1);--无唯一列(或主键)去重,只保留一行,方法3:将重复以及不重复数据去重插入新表,删除旧表相应数据,再把新表数据插入旧表

    SELECT DISTINCT * INTO students2 FROM dbo.students WHERE stu_birthday>='2002-09-01';--条件

    DELETE FROM dbo.students WHERE stu_birthday>='2002-09-01';INSERT INTO dbo.students(stu_name, stu_gender, stu_phone, stu_birthday) SELECT * FROMdbo.students2;DROP TABLE dbo.students2

    展开全文
  • sqlserver 去重

    千次阅读 2019-09-24 16:19:33
    编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
  • sqlserver 去重查询distinct

    千次阅读 2020-12-24 13:39:18
    下面先来看看例子:table表字段1 字段2id name1 a2 b3 c4 c5 b库结构大概这样,这只是一个简单的例子,实际情况会复杂得多。比如我想用一条语句查询得到name不重复的所有数据,那就必须使用distinct去掉多余的重复...
  • select * from(select ROW_NUMBER() over (partition by u.prjviewid order by u.CreateTime desc) numm,* from tbl_option_bd_newzjfx u where u.prjviewid in ( select t.Id from TBL_ITEM t where t.SPAREFIELD15...
  • 整体 select* from BaseDoctor where name in (select name from BaseDoctor group by hiscode,name having count(*) > 1) and code not in (select min(code) from BaseDoctor group by hiscode,name having ...
  • select * from table where id in (select max(id) from table group by [去除重复的字段名列表,....])
  • 还是先上代码吧 ,可以先看 SQL语句去掉重复记录,获取重复记录 代码如下: ALTER procedure [dbo].[PROC_ITEMMASTER_GETUNIQUE] @PAGEINDEX INT,@uid int,@itemnumber varchar(50) AS begin tran –开始事务 drop ...
  • SQL SERVER 查询去重

    2022-01-07 09:57:27
    SQL SERVER 查询去重(distinct,group by,row_bunber() over partition by)
  • sql server语句去重

    千次阅读 2020-07-08 21:50:21
    sql server一般有两种去重方式 1、distinct 使用distinct 进行去重,与oracle 不同的是 sql server中会指定一个字段进行去重 有时候会因为数据与表格问题查询报错 所以一般推荐使用第二种 2、使用函数 ROW_...
  • SQLServer查询去重

    千次阅读 2020-12-10 15:09:28
    但是Distinct只能放在字段的开头,如果多个字段,将不会去重。 SELECT Distinct userid, id FROM UserInfo; 第二种,row_number() over() 函数 SELECT top 10 * FROM ( SELECT * FROM ...
  • 主要介绍了SQL SERVER 分组求和sql语句,需要的朋友可以参考下
  • over SQL Server 通过Row_Number 函数给数据库表的记录进行标号,在使用的时候后面会跟over 子句,而over 子句主要用来对表中的记录进行分组和排序的。 语法如下: ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY ...
  • 在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重记录的所有值。
  • SQL server:查询数据去重除重复值!

    千次阅读 2021-09-26 13:45:37
    第四种: select distinct name from Table_1 第五种: select distinct age from Table_1 大家可以发现去重是根据列的值来执行的, 简言之: 对单个列进行去重时,去除的是指定列中相同的值 对两个列(name,age)...
  • SQL Server 去重处理

    千次阅读 2016-09-03 11:48:43
    DECLARE @BBQC INT --本表去重 DECLARE @GLQC INT --关联去重 DECLARE @BBYS INT --本表余数 DECLARE @YS INT --余数 SELECT TOP 1 @FN = FILEID FROM tAppInfo; SELECT @BBZS = COUNT(*) FROM tAppInfo; -...
  • SQL server去重

    2020-10-25 10:23:37
    有一表data里面的数据有部分是重复的,我想把重复的删除只保留一条,如何实现? [img=https://img-bbs.csdn.net/upload/202010/25/1603592589_782139.jpg][/img]
  • sqlserver记录去重

    2019-01-20 19:35:00
    SQL Server 2005 引入了开窗函数,使得这些经典的难题可以被轻松的解决。  窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。开窗函数分别应用于每个分区,并为每个分区重新启动计算。  ...
  • SQL Server对查询内容去重的语法

    万次阅读 2020-01-10 16:06:04
    SQL Server数据库查询去重 例如我们User表内有四条信息 假如需要对查询的User表内的Name与Sex字段进行去重处理可使用如下语法 但是当你加上Age字段时,发现“张三”又出现了两个。这是因为DISTINCT的语法是将...
  • 数据去重方法1:当表中最在最大流水号时候,我们可以通过关联的方式为每条重复的记录获取唯一值数据去重方法2:为表中记录,按照指定字段进行群组,并获取最大流水号,然后再进行去重操作数据去重方法3:采用分组后,...
  • 根据autoID删除临时表#tmp中的重复数据,只保留每组重复数据中的第一条
  • SQL如何去重

    2020-12-19 15:29:14
    展开全部1、首先创建一个临时表,用于演示sqlserver语法中的去重关键字distinct的使用。本文以sqlserver数据库为例演示,62616964757a686964616fe4b893e5b19e31333431373232IF OBJECT_ID('tempdb..#tmp1') IS NOT ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 15,500
精华内容 6,200
关键字:

sqlserver去重