精华内容
下载资源
问答
  • if not object_id('Tempdb..#T') is null drop table #T ...如果还不能解决问题 建议楼主给出具体需求 是根据哪个字段来去重复的   转载于:https://www.cnblogs.com/langtianya/p/5997048.html
    if not object_id('Tempdb..#T') is null
        drop table #T
    Go
    Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
    Insert #T
    select 1,N'A',N'A1' union all
    select 2,N'A',N'A2' union all
    select 3,N'A',N'A3' union all
    select 4,N'B',N'B1' union all
    select 5,N'B',N'B2'
    Go
     
     
    --I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时,效率高于1、2
    方法1:
    Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)
     
    方法2:
    select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID
     
    方法3:
    select * from #T a where ID=(select min(ID) from #T where Name=a.Name)
     
    方法4:
    select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 
     
    方法5:
    select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)
     
    方法6:
    select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0
     
    方法7:
    select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)
     
    方法8:
    select * from #T a where ID!>all(select ID from #T where Name=a.Name)
     
    方法9(注:ID为唯一时可用):
    select * from #T a where ID in(select min(ID) from #T group by Name)
     
    --SQL2005:
     
    方法10:
    select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
     
    方法11:
     
    select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1
     
    生成结果:
    /*
    ID          Name Memo
    ----------- ---- ----
    1           A    A1
    4           B    B1
     
    (2 行受影响)
    */
     
     
    --II、Name相同ID最大的记录,与min相反:
    方法1:
    Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)
     
    方法2:
    select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID
     
    方法3:
    select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID
     
    方法4:
    select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 
     
    方法5:
    select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)
     
    方法6:
    select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0
     
    方法7:
    select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)
     
    方法8:
    select * from #T a where ID!<all(select ID from #T where Name=a.Name)
     
    方法9(注:ID为唯一时可用):
    select * from #T a where ID in(select max(ID) from #T group by Name)
     
    --SQL2005:
     
    方法10:
    select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
     
    方法11:
    select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1
     
    生成结果2:
    /*
    ID          Name Memo
    ----------- ---- ----
    3           A    A3
    5           B    B2
     
    (2 行受影响)
    */
     
    如果还不能解决问题  建议楼主给出具体需求  是根据哪个字段来去重复的

     

    转载于:https://www.cnblogs.com/langtianya/p/5997048.html

    展开全文
  • oracle根据某个字段去重查询实例

    万次阅读 2016-12-06 16:00:49
    sql去重复

    if not object_id(‘Tempdb..#T’) is null
    drop table #T
    Go
    Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
    Insert #T
    select 1,N’A’,N’A1’ union all
    select 2,N’A’,N’A2’ union all
    select 3,N’A’,N’A3’ union all
    select 4,N’B’,N’B1’ union all
    select 5,N’B’,N’B2’
    Go

    –I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时,效率高于1、2
    方法1:
    Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID

    展开全文
  • oracle查询根据某个字段去重根据另一个字段决定取哪一条数据 简单举例就是在一张学员考试表(student)中有的学员考试参加了多次取最后一次的数据,如表中数据有: 需要得到的结果是: 下面直接给出该查询的SQL...

    oracle查询根据某个字段去重,根据另一个字段决定取哪一条数据
    简单举例就是在一张学员考试表(student)中有的学员考试参加了多次取最后一次的数据,如表中数据有:
    在这里插入图片描述
    需要得到的结果是:
    在这里插入图片描述
    下面直接给出该查询的SQL:

    select * from student s left join (
    select d.studentId,max(d.snum) snum from student d group bu d.studentId
    ) temp on temp.studentId = s.studentId
    where temp.studentId = s.studentId and temp.snum = s.snum
    
    展开全文
  • 问题:在oracle中使用group by分组,group by子句中必须包含所有的select中的字段和order by子句中的字段。 在不使用group by子句的情况下,进行分组。(根据部分字段分组) over()分析函数 原sql SELECT IM. ...
    问题:在oracle中使用group by分组,group by子句中必须包含所有的select中的字段和order by子句中的字段。

    在不使用group by子句的情况下,进行分组。(根据部分字段分组)

    over()分析函数

    原sql

            SELECT
    			IM. ID mediaGroupId,
    			IC. ID cardId,
    			IC.CARD_CONTENT,
    			IC.CARD_TITLE,
    			IM.UPDATE_TIME mediaUpdateTime,
    			IP.SM_URL cardImgUrl,
    			ICB.BUTTON_NAME
    		
    		FROM
    			INFO_MEDIA IM
    		LEFT JOIN INFO_CARD IC ON IM. ID = IC.MEDIA_ID
    		LEFT JOIN INFO_CARD_BUTTON ICB ON IC. ID = ICB.CARD_ID
    		LEFT JOIN INFO_PIC IP ON IP. ID = IC.MEDIA_PIC_ID
    		WHERE
    			IM. ID = '1'
    		ORDER BY
    			IM.UPDATE_TIME DESC
    

    在这里插入图片描述

    我需要根据cardId字段去重,但是看结果还是有重复的。如果使用group by子句的话,是 无法去重 的。因为button_name字段值是不同的,oracle还是会认为两条记录不重复。

    改进sql

        SELECT
    	    *
        FROM
    	(
    		SELECT
    			IM. ID mediaGroupId,
    			IC. ID cardId,
    			IC.CARD_CONTENT,
    			IC.CARD_TITLE,
    			IM.UPDATE_TIME mediaUpdateTime,
    			IP.SM_URL cardImgUrl,
    			ICB.BUTTON_NAME,
    			ROW_NUMBER() OVER (PARTITION BY IC. ID ORDER BY IM.UPDATE_TIME) AS ROW_NUM
    		FROM
    			INFO_MEDIA IM
    		LEFT JOIN INFO_CARD IC ON IM. ID = IC.MEDIA_ID
    		LEFT JOIN INFO_CARD_BUTTON ICB ON IC. ID = ICB.CARD_ID
    		LEFT JOIN INFO_PIC IP ON IP. ID = IC.MEDIA_PIC_ID
    		WHERE
    			IM. ID = '1'
    		ORDER BY
    			IM.UPDATE_TIME DESC
    	) T3
        WHERE T3.ROW_NUM = 1
    

    在这里插入图片描述

    可以看出来,在使用ROW_NUMBER() OVER() 函数之后,重复记录就去掉了。
    ps: over() 函数中order by 子句必须写

    展开全文
  • Oracle某个字段去重

    千次阅读 2020-03-22 21:37:50
    测试数据如下 例子1:要求对NAME相同的值只取一条即可 select * from test t where t.rowid in (select max(rowid) from test group by name) 例子1:要求对NAME相同的值只取一条且AGE值最大 ...
  • 交互式爱情WITH temp as ( select '111' as userid,'5' as status,'7' as ord from dual union all select '111' as userid,'0' as status,'9' as ord from dual union all select '111' as userid,'1'...
  • INSTR于sqlserver 的...GROUP BY 分组去重 SELECT ugrid, ugid,userid,username,face,ctid FROM ct_usergrouprelation where INSTR(strsql,ugid)>0 GROUP BY userid 转载于:https://www.cnblogs.com/xad...
  • select 列1, 列2 from 表名 where rowid in (select max(rowid) from 表名 group by 列2)
  • Oracle 多个字段去重

    千次阅读 2019-05-28 10:36:28
    SELECT DISTINCT SUBSTR(d.DEPT_ID, 0, 2) DEPT,d.SECURITY_CHECK --截取前俩位判断去重复 FROM SAF_T_INSPECTION_DEPT d SELECT A .desc_, A .ID_, B.CHECK_TYPE FROM ( SELECT * ......
  • oracle字段去重

    2012-12-07 23:46:50
    oracle字段去重 以某一个字段去重 oracle字段去重 以某一个字段去重
  • Oracle三种去重的方法 distinct group by rowid 分组函数:作用于一组数据,并对一组数据返回一个值 常见的分组函数有: count 返回找到的记录数 min 返回一个数字列或计算列的最小值 max 返回一个数字列或计算列...
  • oracle查询对某个字段进行去重处理

    万次阅读 2019-08-21 10:10:21
    oracle查询对某个字段进行去重处理需要使用distinct函数 比如从A表查询aa字段和bb字段,然后对aa字段进行去重,查询语句如下: SELECT distinct(a.aa) as aa,a.bb as bb FROM A a 提示:去重的字段必须放在第一...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,872
精华内容 1,948
关键字:

oracle根据某个字段去重