TB1SBID NAME
1 张三
2 李四
3 王五
4 赵六TB2SBID NAME
8 张三
9 李四
3 王五
4 赵六找出两表SBID值不同的项
效果:
SBID NAME
1 张三
2 李四
1 张三
2 李四
3 王五
4 赵六TB2SBID NAME
8 张三
9 李四
3 王五
4 赵六找出两表SBID值不同的项
效果:
SBID NAME
1 张三
2 李四
from a
where exists(select 1 from b where b.name = a.name and b.SBID <> a.SBID)
1 张兴
2 李龙
3 王五
4 赵六 TB2 SBID NAME
8 张三
9 李四
3 王五
4 赵六 找出两表SBID值不同的项
效果:
SBID NAME
1 张兴
2 李龙
from TB1,TB2
where TB1.SBID<>TB2.SBID
union
select TB2.SBID, TB2.NAME
from TB1,TB2
where TB1.SBID<>TB2.SBID
服务器: 消息 205,级别 16,状态 1,行 1
包含 UNION 运算符的 SQL 语句中的所有查询都必须在目标列表中具有相同数目的表达式。
expect select * from b
declare @TB1 table(SBID int, NAME varchar(10))
insert into @TB1
select 1, '张三' union all
select 2, '李四' union all
select 3, '王五' union all
select 4, '赵六' declare @TB2 table(SBID int, NAME varchar(10))
insert into @TB2
select 8, '张三' union all
select 9, '李四' union all
select 3, '王五' union all
select 4, '赵六' select * from @TB1 t1
where NAME not in(select NAME from @TB2 where NAME=t1.NAME and SBID<>t1.SBID)/*
SBID NAME
3 王五
4 赵六
*/
select * from a
except select * from b
试试看
drop table tb1
if object_id('tb2') is not null
drop table tb2
create table tb1(SBID int, NAME varchar(10))
insert into tb1
select 1, '张三' union all
select 2, '李四' union all
select 3, '王五' union all
select 4, '赵六' create table tb2(SBID int, NAME varchar(10))
insert into tb2
select 8, '张三' union all
select 9, '李四' union all
select 3, '王五' union all
select 4, '赵六' select * from tb1
where name not in
(select tb1.name from tb1,tb2
where tb1.SBID<>tb2.SBID and tb1.name=tb2.name)/*结果
3 王五
4 赵六
*/
DECLARE @TB1 TABLE
(
SBID INT ,
NAME VARCHAR(50)
)DECLARE @TB2 TABLE
(
SBID INT ,
NAME VARCHAR(50)
)INSERT @TB1 (
[SBID],
[NAME]
) SELECT 1 ,'张三'
UNION ALL SELECT 2, '李四'
UNION ALL SELECT 3, '王五'
UNION ALL SELECT 4, '赵六' INSERT @TB2 (
[SBID],
[NAME]
) SELECT 8 ,'张三'
UNION ALL SELECT 9, '李四'
UNION ALL SELECT 3, '王五'
UNION ALL SELECT 4, '赵六'
SELECT a.* FROM @TB1 a LEFT JOIN @TB2 b ON a.[SBID] = b.[SBID]
WHERE b.[SBID] IS NULL
insert into @TB1
select 1, '张三' union all
select 2, '李四' union all
select 3, '王五' union all
select 4, '赵六' declare @TB2 table(SBID int, NAME varchar(10))
insert into @TB2
select 8, '张三' union all
select 9, '李四' union all
select 3, '王五' union all
select 4, '赵六' select t1.* from @TB1 t1
join @tb2 T2 On T1.Name=T2.Name
Where T1.SbId<>T2.SbId
declare @tb1 table(sbid int,[name] varchar(50))
insert into @tb1
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六'declare @tb2 table(sbid int,[name] varchar(50))
insert into @tb2
select 8,'张三' union all
select 9,'李四' union all
select 3,'王五' union all
select 4,'赵六'select a.name from @tb1 as a,@tb2 as b where a.name=b.name and a.sbid<>b.sbid
应该是
select * from a
INTERSECT
select * from b
可以考虑设置NAME为主键!
然后select sbid,name from tb1
where tb1.name=tb2.name