34,594
社区成员
发帖
与我相关
我的任务
分享
表1
reqeustfilesystem
字段
fileid(主键) requestfilename
1 ab
2 bc
表2
programfilesystem
字段
exeid(主键) fileid(和表1的对应) programfilename
1 1 a.doc
2 1 b.doc
3 2 a.doc
-------------------
其中*id都为数值型
select a.programfilename from programfilesystem as a where a.fileid=(select fileid from requestfilesystem as b where a.exeid=2 and a.fileid=b.fileid)
这一句的结果相当于
select programfilename from programfilesystem where exeid=2
我居然不明白为什么这样
use tempdb
go
declare @requestfilesystem table
(
fileid int identity(1,1),
requestfilename varchar(10)
);
declare @programfilesystem table
(
exeid int,
fieldid int,
programfilename varchar(50)
);
insert into @requestfilesystem values('ab')
insert into @requestfilesystem values('bc')
insert into @programfilesystem values(1,1,'a.doc')
insert into @programfilesystem values(2,1,'b.doc')
insert into @programfilesystem values(3,2,'b.doc')
--preparing update exeid=2
-- variable exeid=2
;With CTE
as
(
Select exeid, programfilename
from @programfilesystem p
where p.fieldid=(select fieldid from @programfilesystem where exeid=2)
)
--在这里找到所有不能修改的名字,
/*假设要修改的记录为exeid=2,那么除exeid=2以外的所有记录名称都不符合条件*/
select programfilename
from CTE
where exeid<>2
select a.programfilename from programfilesystem as a where a.fileid=(select b.fileid from programfilesystem as b where a.fileid=b.fileid and b.exeid=2) and programfilename='a.doc'
select
*
from
programfilesystem a
where
exists(select 1 from requestfilesystem where fileid=a.fileid and programfilename='a.doc')