Question

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

如：table A id name number
1 a 123
2 b 152
3 c 123
4 d 520
5 e 300
现在查询number字段不重复的数据，也就是结果只想要 id为 2,4,5的数据，请问sql怎么写？

额，没分了，不好意思，谢谢大家了

Answer 1

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);

Answer 2

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 行受影响)

Answer 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))

Answer 4

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

Answer 5

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