【Oracle】关于去重问题

1、创建demo表

CREATE TABLE DEMO_01(
D_NO VARCHAR2(10 CHAR),
D_NAME VARCHAR2(10 CHAR),
D_TYPE CHAR(2)
);

2、对D_NAME,D_TYPE,查询结果去重

SELECT DISTINCT D_NAME , D_TYPE FROM DEMO_01;

3、删除重复数据

方式一

SELECT T1.* FROM DEMO_01 T1
WHERE T1.D_NO NOT IN (
	SELECT MAX(D_NO) D_NO FROM DEMO_01 GROUP BY D_NAME ,D_TYPE
)	

方式二（Oracle用）

SELECT  T1.ROWID,T1.*  FROM DEMO_01 T1
WHERE T1.ROWID !=(
	SELECT MAX(T2.ROWID) FROM DEMO_01 T2
						WHERE T1.D_NAME = T2.D_NAME 
						  AND T1.D_TYPE = T2.D_TYPE
				 )