SQL 查询表中某一字段不重复的所有数据

1、创建测试表，

create table test_dis(id number, name varchar2(20), value number(10));

2、插入测试数据

insert into test_dis values(1,'a',123);

insert into test_dis values(2,'b',152);

insert into test_dis values(3,'c',123); 

insert into test_dis values(4,'d',520);

insert into test_dis values(5,'e',300);

commit;

3、查询表中全量数据，select t.*, rowid from test_dis t;

4、编写sql，查询表中某一字段不重复的所有数据，可以发现只有id为2，4，5的记录查询出。

   select * from test_dis t where value in (select value from test_dis group by value having count(*)=1);

CREATE TABLE #A (
id INT,
name varchar(4),
number INT
);

INSERT INTO #A
SELECT 1, 'a', 123 UNION ALL
SELECT 2, 'b', 152 UNION ALL
SELECT 3, 'c', 123 UNION ALL
SELECT 4, 'd', 520 UNION ALL
SELECT 5, 'e', 300;
GO

SELECT
*
FROM
#A main
WHERE
NOT EXISTS (
SELECT 1
FROM #A sub
WHERE main.id <> sub.id AND main.number = sub.number
);
GO

id name number
----------- ---- -----------
2 b 152
4 d 520
5 e 300
(3 行受影响)

　　SELECT *
　　FROM tablea
　　WHERE (number NOT IN
　　(SELECT ta.number
　　FROM tablea AS ta INNER JOIN
　　 tablea AS tb ON ta.number = tb.number AND ta.id <> tb.id))

select * from 表名 where number in
( select number from 表名 group by number having count(id) = 1 )

select * from tablea where numer in(select number from tablea group by number having count(1)=1)