请教SQL大神们一个问题
有两个表A和B,主键都是物品号(WPH),两表主键相同,两个表中都有字段存货仓库号(CHCK),我想查询B表中CHCK这个字段中的数据和A表中CHCK数据不一样的那些物品...
有两个表A和B,主键都是物品号(WPH),两表主键相同,两个表中都有字段存货仓库号(CHCK),我想查询B表中CHCK这个字段中的数据和A表中CHCK数据不一样的那些物品号,大神们帮下忙,万分感谢!
展开
6个回答
展开全部
Try this one:
select WPH
FROM B t
WHERE EXISTS (SELECT 1 FROM A where WPH = t.WPH and CHCK <> t.CHCK)
select WPH
FROM B t
WHERE EXISTS (SELECT 1 FROM A where WPH = t.WPH and CHCK <> t.CHCK)
展开全部
select * from B
where exists (select 1 from a where a.wph=b.wph and a.chck<>b.chck);
where exists (select 1 from a where a.wph=b.wph and a.chck<>b.chck);
展开全部
select B.chck
from A,B
where A.wph=B.wph and A.chck<>B.chck
from A,B
where A.wph=B.wph and A.chck<>B.chck
展开全部
select b.wph from a,b where a.check<>b.check and a.wph=b.wph.
追问
这个我试过了,不对的,您再帮忙想想!
追答
你是不是想查check只在b 表中,不在a表中的数据。
如果按你止面说的,楼上查询的表应该是b之外应该没错,我的也没错
不然就是
select b.wph from b where b.check not in (select distinct a.check from a);
展开全部
create table tb_course(
id int auto_increment primary key,
name varchar(20)
);
create table tb_stu(
id int auto_increment primary key,
name varchar(20),
age varchar(5),
sex varchar(5),
address varchar(20)
);
create table stu_cou(
sid int(11),
cid int(11)
);
-- 添加外键约束
/* 添加外键约束 */
insert into tb_course(name) values('tt');
insert into tb_course(name) values('ttt');
insert into tb_course(name) values('ss');
insert into tb_course(name) values('xx');
insert into tb_stu(name,age) values('tt','22');
insert into tb_stu(name,age) values('ttt','22');
insert into tb_stu(name,age) values('mm','22');
insert into tb_stu(name,age) values('ms','22');
alter table stu_cou add constraint stu_cou_FK foreign key (sid) references tb_stu(id);
alter table stu_cou add constraint cou_stu_FK foreign key (cid) references tb_course(id);
-- 查询2张表相同字段不同的名称的值
select c.name,s.name from
tb_course c join tb_stu s on
s.name not in (select c.name from tb_course c)
where c.id=s.id ;
-- 查询一张表与另一张表相同字段不同名称的值
select s.name from tb_stu s
where exists (select * from tb_course c where c.id=s.id and c.name<>s.name);
id int auto_increment primary key,
name varchar(20)
);
create table tb_stu(
id int auto_increment primary key,
name varchar(20),
age varchar(5),
sex varchar(5),
address varchar(20)
);
create table stu_cou(
sid int(11),
cid int(11)
);
-- 添加外键约束
/* 添加外键约束 */
insert into tb_course(name) values('tt');
insert into tb_course(name) values('ttt');
insert into tb_course(name) values('ss');
insert into tb_course(name) values('xx');
insert into tb_stu(name,age) values('tt','22');
insert into tb_stu(name,age) values('ttt','22');
insert into tb_stu(name,age) values('mm','22');
insert into tb_stu(name,age) values('ms','22');
alter table stu_cou add constraint stu_cou_FK foreign key (sid) references tb_stu(id);
alter table stu_cou add constraint cou_stu_FK foreign key (cid) references tb_course(id);
-- 查询2张表相同字段不同的名称的值
select c.name,s.name from
tb_course c join tb_stu s on
s.name not in (select c.name from tb_course c)
where c.id=s.id ;
-- 查询一张表与另一张表相同字段不同名称的值
select s.name from tb_stu s
where exists (select * from tb_course c where c.id=s.id and c.name<>s.name);
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询