表1:id c1 c2 c3 c4
1 a a a a
2 c d b b
3 c c a d
4 a a a a
5 c c a d
6 d d d a
7 c d b b
表2:
id c1 c2 c3 c4
1 a a a a
2 c c a d
以表1为基础表,需查询出在表1且不在表2,且c1、c2、c3、c4必须完全相同比如上面的例子,查询结果为
c d b b
d d d a
1 a a a a
2 c d b b
3 c c a d
4 a a a a
5 c c a d
6 d d d a
7 c d b b
表2:
id c1 c2 c3 c4
1 a a a a
2 c c a d
以表1为基础表,需查询出在表1且不在表2,且c1、c2、c3、c4必须完全相同比如上面的例子,查询结果为
c d b b
d d d a
解决方案 »
- 用log explorer 把数据库恢复到指定时间的操作
- 请帮忙看看这个sql如何写?谢谢
- 关于EXEC同时调用多个存储过程的执行顺序
- 请问高手,SQLServer表的字段数目有什么限制吗?
- 求多表查询有SQL语句!
- 求助多表查询的SQL!
- 从日志备份恢复失败,提示:LSN 太晚,无法应用到数据库
- 请教: 如何根据表中Identity类型的字段的值查询出最后n条记录?
- c/s的mis做好后,要生成安装文件了,sql server里的数据库怎么版??一般公司开发的mis这时候怎么处理的??//牛虻
- 关于SQL SERVER客户端监控的程序的问题
- 清空表,bcp导入数据,数据混乱问题
- 请教大侠怎么正确使用alter table rename to来修改表字段和表名!
SELECT 1 FROM T2 WHERE T2.C1=T1.C1 AND T2.C2=T1.C2 AND T2.C3=T1.C3 AND T2.C4=T1.C4
)
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:表1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'表1')
AND type in (N'U'))
DROP TABLE 表1
GO---->建表
create table 表1([id] int,[c1] varchar(1),[c2] varchar(1),[c3] varchar(1),[c4] varchar(1))
insert 表1
select 1,'a','a','a','a' union all
select 2,'c','d','b','b' union all
select 3,'c','c','a','d' union all
select 4,'a','a','a','a' union all
select 5,'c','c','a','d' union all
select 6,'d','d','d','a' union all
select 7,'c','d','b','b'
GO--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:表2
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'表2')
AND type in (N'U'))
DROP TABLE 表2
GO---->建表
create table 表2([id] int,[c1] varchar(1),[c2] varchar(1),[c3] varchar(1),[c4] varchar(1))
insert 表2
select 1,'a','a','a','a' union all
select 2,'c','c','a','d'
GO--> 查询结果
SELECT c1,c2,c3, c4 FROM 表1
except
SELECT c1,c2,c3, c4 FROM 表2
--> 删除表格
--DROP TABLE 表2
drop table t1
Go
Create table t1([id] int,[c1] nvarchar(1),[c2] nvarchar(1),[c3] nvarchar(1),[c4] nvarchar(1))
Insert t1
select 1,N'a',N'a',N'a',N'a' union all
select 2,N'c',N'd',N'b',N'b' union all
select 3,N'c',N'c',N'a',N'd' union all
select 4,N'a',N'a',N'a',N'a' union all
select 5,N'c',N'c',N'a',N'd' union all
select 6,N'd',N'd',N'd',N'a' union all
select 7,N'c',N'd',N'b',N'b'
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([id] int,[c1] nvarchar(1),[c2] nvarchar(1),[c3] nvarchar(1),[c4] nvarchar(1))
Insert t2
select 1,N'a',N'a',N'a',N'a' union all
select 2,N'c',N'c',N'a',N'd'
Go
select *
from t1 t
where not exists(select 1
from t2
where [c1]=t.[c1] and [c2]=t.[c2] and [c3]=t.[c3] and [c4]=t.[c4])
/*
id c1 c2 c3 c4
----------- ---- ---- ---- ----
2 c d b b
6 d d d a
7 c d b b(3 row(s) affected)*/
except(两个集合相减)
select * from table1
except
select * from table2
SELECT c1,c2,c3,c4 FROM #a a
EXCEPT
SELECT c1,c2,c3,c4 FROM #b bc1 c2 c3 c4
---- ---- ---- ----
c d b b
d d d a(2 row(s) affected)
insert into t1 select 1,'a','a','a','a'
insert into t1 select 2,'c','d','b','b'
insert into t1 select 3,'c','c','a','d'
insert into t1 select 4,'a','a','a','a'
insert into t1 select 5,'c','c','a','d'
insert into t1 select 6,'d','d','d','a'
insert into t1 select 7,'c','d','b','b'
create table t2(id int,c1 nvarchar(10),c2 nvarchar(10),c3 nvarchar(10),c4 nvarchar(10))
insert into t2 select 1,'a','a','a','a'
insert into t2 select 2,'c','c','a','d'
go
select c1,c2,c3,c4 from t1 a where not exists(select 1 from t2 where c1=a.c1 and c2=a.c2 and c3=a.c3 and c4=a.c4)
go
drop table t1,t2
/*
c1 c2 c3 c4
---------- ---------- ---------- ----------
c d b b
d d d a
c d b b(3 行受影响)
*/