27,579
社区成员
发帖
与我相关
我的任务
分享
--创建表
use pubs
if object_id('fa') is not null drop table fa
create table fa
(
mid int,
mname varchar(20)
)
go
if object_id('fb') is not null drop table fb
create table fb
(
mid int,
mname varchar(20)
)
go
--插入数据
insert into fa
select '1001','张三' union all
select '1002','李四' union all
select '1003','王五' union all
select '1004','刘六'
--select * from fa
insert into fb
select '1001','王五' union all
select '1003','李四' union all
select '1004','张三' union all
select '1005','刘六'
--select * from fb
---
---所求的查询语句
---
--附注:原题目中,表fb是没有mid为1002的,且表fb中mid对应的张三之类的名字和表fa的所对应的是不相同滴吧
---要求查询结果:(好像是1001,1002,1003,或者是1001,1002,1004?显示的是表fa的)
---1001 张三
---1002 李四
---1003 王五
--删除用例表
--drop table fa
--drop table fb
if object_id('fa') is not null drop table fa
create table fa
(
mid int,
mname varchar(20)
)
go
if object_id('fb') is not null drop table fb
create table fb
(
mid int,
mname varchar(20)
)
go
--插入数据
insert into fa
select '1001','张三' union all
select '1002','李四' union all
select '1003','王五' union all
select '1004','刘六'
--select * from fa
insert into fb
select '1001','王五' union all
select '1003','张三' union ALL --估计是这个李四跟下面的张三位置对调一下吧
select '1004','李四' union all
select '1005','刘六'
SELECT *
FROM fa a
WHERE EXISTS(
SELECT 1
FROM fb b
WHERE (SELECT COUNT(1) FROM fb WHERE mid<=b.mid)
= (SELECT COUNT(1) FROM fa WHERE mid<=a.mid)
AND a.mname<>b.mname)
/*
mid mname
----------- --------------------
1001 张三
1002 李四
1003 王五
(3 行受影响)
*/