插入及查询不重复数据的几种情况

WITH A AS
(
SELECT Num
FROM
(VALUES (1),(2),(3),(2)) AS X(Num)
)
SELECT * FROM A;
GO

---输出：
---Num
---1
---2
---3
---2

WITH A AS
(
SELECT Num
FROM
(VALUES (1),(2),(3),(2)) AS X(Num)
GROUP BY Num
HAVING COUNT(NUM) = 1
)
SELECT * FROM A;
GO
---输出：
---Num
---1
---3

 

WITH T AS
(
SELECT Num,Word
FROM
(VALUES (1, 1, 'K'),(2, 2, 'H'),(3, 3, 'J'),(4, 2, 'N')) AS Y (ID, Num, Word)
)
SELECT * FROM T
GROUP BY Num,Word
HAVING COUNT(NUM) = 1;
GO

---输出：
--- Num Word
---1 K
---2 H
---3 J
---2 N

WITH T AS
(
SELECT Num,Word
FROM
(VALUES (1, 1, 'K'),(2, 2, 'H'),(3, 2, 'J'),(4, 2, 'N')) AS Y (ID, Num, Word)
)
SELECT * FROM T
WHERE Num  IN (
SELECT Num FROM T
GROUP BY Num
HAVING COUNT(NUM) = 1)
GO
---输出
---Num Word
---1 K

WITH T AS
(
SELECT ID, Num,Word
FROM
(VALUES (1, 1, 'K'),(2, 2, 'H'),(3, 2, 'J'),(4, 2, 'H')) AS Y (ID, Num, Word)
)
SELECT * FROM T AS T0
WHERE NOT EXISTS (
SELECT 1 FROM T AS T1
WHERE T0.Num = T1.Num AND T0.Word = T1.Word AND T0.ID != T1.ID)
GO
--输出：
---id   Num Word
---1 1 K
---3 2 J

---全集- 相同
WITH T AS
(
SELECT ID,Num,Word
FROM
(VALUES (1, 1, 'K'),(2, 2, 'H'),(3, 2, 'J'),(4, 2, 'N')) AS Y (ID, Num, Word)
)
SELECT * FROM T
EXCEPT
SELECT * FROM T
WHERE Num not IN (
SELECT Num FROM T
GROUP BY Num
HAVING COUNT(NUM) = 1)
GO

--输出：
---ID   Num Word
---1 1 K

declare @t table (id int identity(1,1),a1 varchar(10), b1 varchar(10), c1 varchar(10), d1 varchar(10))

insert into @t values ('aaa','bbbb','cccc','bbd')
insert into @t values ('bb', 'bbbb', 'cccc', 'cc')
insert into @t values ('dd', 'aaa', 'bcs', 'dass')
insert into @t values ('ee', 'dda', 'dgd', 'hhh')
insert into @t values ('ff', 'adgdg', 'dfasdf', 'sed')
insert into @t values ('dd', 'adgdg', 'dfasdf', 'dsdf')
select distinct c.* from
(
select a.* from @t a
where
a.id in
(
select top 1 b.id from @t b
where b.b1=a.b1
and b.b1 in
(
select b1
from @t
group by b1,c1
having count(1)>=1
)
order by b.id
)
or
b1 not in
(
select b1
from @t
group by b1,c1
having count(1)>1
)
) c

order by id

---输出：
---1 aaa bbbb cccc bbd
---3 dd aaa bcs dass
---4 ee dda dgd hhh
---5 ff adgdg dfasdf sed