插入及查询不重复数据的几种情况
来源:互联网 发布:好用用打谱软件 编辑:程序博客网 时间:2024/06/01 08:04
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
- 插入及查询不重复数据的几种情况
- 查询重复数据的几种方式
- 向表中插入数据的几种情况
- 插入不重复数据
- 插入不重复数据
- sqlserver中插入不重复的数据!
- Oracle 查询重复数据的几种SQL
- 查询不重复数据
- 查询不重复数据
- SQL 查询 标识不重复的数据
- MySQL 插入不重复数据
- mysql查询字段数据不重复distinct及distinct的多字段操作
- sql 查询不重复数据
- mysql查询不重复数据
- oracle查询不重复数据
- 插入MySQL数据库前去除重复数据的几种方法
- SQL查询重复的几种方法
- MYSQL中查找重复记录的几种情况
- 贪心算法
- 数据库原理
- 余凯老师发表的:深度学习: 推进人工智能的梦想
- SQL语句优化技巧
- 如何评估作为嵌入式软件工程师的级别!
- 插入及查询不重复数据的几种情况
- POJ 1391 Erdos Numbers 最短路径
- 数据库2
- 关于:宏 (带参数的宏定义)
- go实现留言本
- uestc oj 1035 论文搜索
- POJ1041 John's trip
- 搭建 eclipse pydev python 开发环境 on mac and windows
- 在codeblocks下的文件读写