34,576
社区成员
发帖
与我相关
我的任务
分享
--用游标
declare @id int
declare DZCursor CURSOR for select id from project where flag = true
open DZCursor
fetch next from DZCursor into @id
while @@fetch_status=0
begin
select * from test1 where projectid =@id
fetch next from DZCursor into @id
end
close DZCursor
deallocate DZCursor
create table project(id int, projectName varchar(10), flag varchar(10))
go
insert project select 0 , '项目1' , 'true'
insert project select 1 , '项目2' , 'true'
insert project select 2 , '项目3' , 'false'
insert project select 3 , '项目4' , 'true'
create table test1(id int, name varchar(10), projectid int)
go
insert test1 select 1 , '张三' , 0
insert test1 select 2 , '张三' , 1
go
create table test2(id int, name varchar(10), projectid int)
insert test2 select 1 , '李四' , 2
insert test2 select 2 , '李四' , 3
go
--传入true,结果如下:
select b.projectname,a.name from test1 a,project b where a.projectid=b.id and a.projectid in (select id from project where flag='true')
union all
select b.projectname,a.name from test2 a,project b where a.projectid=b.id and a.projectid in (select id from project where flag='true')
/*
projectname name
----------- ----------
项目1 张三
项目2 张三
项目4 李四
(所影响的行数为 3 行)
*/
--传入flase 结果如下:
select b.projectname,a.name from test1 a,project b where a.projectid=b.id and a.projectid in (select id from project where flag='false')
union all
select b.projectname,a.name from test2 a,project b where a.projectid=b.id and a.projectid in (select id from project where flag='false')
/*
projectname name
----------- ----------
项目3 李四
(所影响的行数为 1 行)
*/
if object_id('project') is not null drop table project
go
create table project([id] int,[projectName] varchar(10),FLAG varchar(10))
insert project select 0,'项目1','TRUE'
union all select 1,'项目2','TRUE'
union all select 2,'项目3','FALSE'
union all select 3,'项目4','TRUE'
go
if object_id('test1') is not null drop table test1
go
create table test1([id] int,[name] varchar(10),[projectid] int)
insert test1 select 1,'张三',0
union all select 2,'张三',1
go
if object_id('test2') is not null drop table test2
go
create table test2([id] int,[name] varchar(10),[projectid] int)
insert test2 select 1,'李四',2
union all select 2,'李四',3
go
select a.projectName,b.Name
from project a
join (select * from test1 union all select * from test2) b
on a.id=b.[projectid]
where flag='true'
/*
projectName Name
----------- ----------
项目1 张三
项目2 张三
项目4 李四
(3 行受影响)
*/
select a.projectName,b.Name
from project a
join (select * from test1 union all select * from test2) b
on a.id=b.[projectid]
where flag='false'
/*
projectName Name
----------- ----------
项目3 李四
(1 行受影响)
*/
declare @flag int
set @flag = 1 -- or 0
select A.projectName,B.name
from project as A
LEFT JOIN Test1 AS B
ON A.projectid = B.projectid
WHERE A.flag = @flag
UNION ALL
select C.projectName,D.name
from project as C
LEFT JOIN Test1 AS D
ON C.projectid = D.projectid
WHERE C.flag = @flag