用mssql查询 a 表存在,b表不存在的数据!
如:a 表的数据: b表的数据:
aid aname apassword bid bname bpassword
1 fsd sfdsfsd 1 ere sdfsdfs
2 sdf sdfsd 2 sdf sdfsdf
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf要求显示信息为:
aid aname apassword
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf 不用可以用了查询
他好像有提示用左连,但我还是没做出来!
如:a 表的数据: b表的数据:
aid aname apassword bid bname bpassword
1 fsd sfdsfsd 1 ere sdfsdfs
2 sdf sdfsd 2 sdf sdfsdf
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf要求显示信息为:
aid aname apassword
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf 不用可以用了查询
他好像有提示用左连,但我还是没做出来!
godeclare @a table
(
aid int,
aname varchar(10),
apassword varchar(10)
);
insert into @a values(1,'fsd','sfdsfsd')
insert into @a values(2,'sdf','sdfsd')
insert into @a values(3,'sdf','3rwesef')
insert into @a values(4,'sed','sdfsdfd')
insert into @a values(5,'3de','sdfsdf')declare @b table
(
bid int,
bname varchar(10),
bpassword varchar(10)
);
insert into @b values(1,'ere','sdfsdfs')
insert into @b values(2,'sdf','sdfsdf')/*
要求显示信息为:
aid aname apassword
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf
*/select *
from @a
where aid not in
(select aid
from @a a
join @b b on a.aid=b.bid)
--2.连接
Select d.*
from
(
select aid
from @a
except
select bid
from @b) c
join @a d on c.aid=d.aid
declare @a table
(
aid int,
aname varchar(10),
apassword varchar(10)
);
insert into @a values(1,'fsd','sfdsfsd')
insert into @a values(2,'sdf','sdfsd')
insert into @a values(3,'sdf','3rwesef')
insert into @a values(4,'sed','sdfsdfd')
insert into @a values(5,'3de','sdfsdf')declare @b table
(
bid int,
bname varchar(10),
bpassword varchar(10)
);
insert into @b values(1,'ere','sdfsdfs')
insert into @b values(2,'sdf','sdfsdf')
select * from @a where aid not in(select bid from @b)
declare @a table
(
aid int,
aname varchar(10),
apassword varchar(10)
);
insert into @a values(1,'fsd','sfdsfsd')
insert into @a values(2,'sdf','sdfsd')
insert into @a values(3,'sdf','3rwesef')
insert into @a values(4,'sed','sdfsdfd')
insert into @a values(5,'3de','sdfsdf')declare @b table
(
bid int,
bname varchar(10),
bpassword varchar(10)
);
insert into @b values(1,'ere','sdfsdfs')
insert into @b values(2,'sdf','sdfsdf')select * from @a where aid not in(select bid from @b)---或select * from @a t1 where not exists(select 1 from @b where t1.aid=bid)
SELECT * FROM a WHERE aid NOT IN(SELECT bid FROM b)
(
aid int,
aname varchar(10),
apassword varchar(10)
);
insert into @a values(1,'fsd','sfdsfsd')
insert into @a values(2,'sdf','sdfsd')
insert into @a values(3,'sdf','3rwesef')
insert into @a values(4,'sed','sdfsdfd')
insert into @a values(5,'3de','sdfsdf')declare @b table
(
bid int,
bname varchar(10),
bpassword varchar(10)
);
insert into @b values(1,'ere','sdfsdfs')
insert into @b values(2,'sdf','sdfsdf')SELECT * FROM @a WHERE NOT exists(SELECT 1 FROM @b where bid=aid)
aid aname apassword
----------- ---------- ----------
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf
declare @a table
(
aid int,
aname varchar(10),
apassword varchar(10)
);
insert into @a values(1,'fsd','sfdsfsd')
insert into @a values(2,'sdf','sdfsd')
insert into @a values(3,'sdf','3rwesef')
insert into @a values(4,'sed','sdfsdfd')
insert into @a values(5,'3de','sdfsdf')declare @b table
(
bid int,
bname varchar(10),
bpassword varchar(10)
);
insert into @b values(1,'ere','sdfsdfs')
insert into @b values(2,'sdf','sdfsdf')select a.aid,a.aname,a.apassword from @a a
left join @b b on a.aid=b.bid
where not exists(select 1 from @b b where a.aid=b.bid)
--//结果
/*
aid aname apassword
----------- ---------- ----------
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf(3 行受影响)*/
select * from a where checksum(*) not in (select checksum(*) from b)select a.* from a left join b on a.aid=b.bid where b.bid is null
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-14 09:22:48
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([aid] int,[aname] varchar(3),[apassword] varchar(7))
insert [a]
select 1,'fsd','sfdsfsd' union all
select 2,'sdf','sdfsd' union all
select 3,'sdf','3rwesef' union all
select 4,'sed','sdfsdfd' union all
select 5,'3de','sdfsdf'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([bid] int,[bname] varchar(3),[bpassword] varchar(7))
insert [b]
select 1,'ere','sdfsdfs' union all
select 2,'sdf','sdfsdf'
--------------开始查询--------------------------
select a.* from a left join b on a.aid=b.bid where b.bid is null
----------------结果----------------------------
/* aid aname apassword
----------- ----- ---------
3 sdf 3rwesef
4 sed sdfsdfd
5 3de sdfsdf(3 行受影响)
*/
select a.*
from a left join b on a.aid=b.bid
where b.bid is null
我用SQL2005查询,用的也是你用那个SQL语句,可是不行,查不到的!
返回的是A表全部,B表的全不为NULL
你应该说:用mssql查询 aid在 a 表存在,而在 b表不存在的数据!
如果不允许用子查询,那么我第二个方法用了except半连接和join内连接,可否,请你再看看,check一下
Select d.*
from
(
select aid
from @a
except
select bid
from @b) c
join @a d on c.aid=d.aid
可否?
use tempdb
godeclare @a table
(
aid int,
aname varchar(10),
apassword varchar(10)
);
insert into @a values(1,'fsd','sfdsfsd')
insert into @a values(2,'sdf','sdfsd')
insert into @a values(3,'sdf','3rwesef')
insert into @a values(4,'sed','sdfsdfd')
insert into @a values(5,'3de','sdfsdf')declare @b table
(
bid int,
bname varchar(10),
bpassword varchar(10)
);
insert into @b values(1,'ere','sdfsdfs')
insert into @b values(2,'sdf','sdfsdf')select *
from @a a
left join @b b
on a.aid=b.bid
where bid is null
如果你非要使用左连接,就像中国风说的那样也可以!