A 表 文件表
fileid filename userid folderid filecontent
1 aaa 11 60 ntext
2 bbb 11 60 ntext
3 bbb 11 60 ntext
4 ccccc 11 60 ntext
5 ddd 12 60 ntext
6 eeee 12 60 ntextB 表 权限表
folderid userid read del
60 11 1 1
60 12 1 1
60 13 1 1
===================================================
求一个sql 要求如果a表中userid 是@userid 或者 该@userid 在b表 中 该文件id对应的 folderid的 read 为 1 都能显示
例如 @userid =13 或者 11 或者12 都会有如下显示
1 aaa 11 60 ntext
2 bbb 11 60 ntext
3 bbb 11 60 ntext
4 ccccc 11 60 ntext
5 ddd 12 60 ntext
6 eeee 12 60 ntext在线急等!!!!!!
fileid filename userid folderid filecontent
1 aaa 11 60 ntext
2 bbb 11 60 ntext
3 bbb 11 60 ntext
4 ccccc 11 60 ntext
5 ddd 12 60 ntext
6 eeee 12 60 ntextB 表 权限表
folderid userid read del
60 11 1 1
60 12 1 1
60 13 1 1
===================================================
求一个sql 要求如果a表中userid 是@userid 或者 该@userid 在b表 中 该文件id对应的 folderid的 read 为 1 都能显示
例如 @userid =13 或者 11 或者12 都会有如下显示
1 aaa 11 60 ntext
2 bbb 11 60 ntext
3 bbb 11 60 ntext
4 ccccc 11 60 ntext
5 ddd 12 60 ntext
6 eeee 12 60 ntext在线急等!!!!!!
----------
??
(
fileid int,
[filename] varchar(20),
userid int,
folderid int,
filecontent ntext
)
insert into #FileInfo select 1,'aaa',11,60,'ntext'
union all select 2,'bbb',11,60,'ntext'
union all select 3,'bbb',11,60,'ntext'
union all select 4,'ccccc',11,60,'ntext'
union all select 5,'ddd',12,60,'ntext'
union all select 6,'eeee',12,60,'ntext' create table #Role
(
folderid int,
userid int,
[read] int,
del int
)
insert into #Role select 60,11,1,1
union all select 60,12,1,1
union all select 60,13,1,1select F.fileid,F.[filename],F.userid,F.folderid,F.filecontent from #FileInfo F
join #Role R on F.userid=R.userid and R.[read]=1
但是那个权限表中的read 为 1
go
create table #FileInfo
(
fileid int,
[filename] varchar(20),
userid int,
folderid int,
filecontent varchar(20)
)
insert into #FileInfo select 1,'aaa',11,60,'ntext'
union all select 2,'bbb',11,60,'ntext'
union all select 3,'bbb',11,60,'ntext'
union all select 4,'ccccc',11,60,'ntext'
union all select 5,'ddd',12,60,'ntext'
union all select 6,'eeee',12,60,'ntext'
if object_id('tempdb..#role')is not null drop table #role
go
create table #Role
(
folderid int,
userid int,
[read] int,
del int
)
insert into #Role select 60,11,1,1
union all select 60,12,1,1
union all select 60,13,1,1
declare @userid int
set @userid =12
select distinct f.* from #fileInfo f inner join #role r
on f.userid=@userid or (r.userid=@userid and r.[read]=1)
/*fileid filename userid folderid filecontent
----------- -------------------- ----------- ----------- --------------------
1 aaa 11 60 ntext
2 bbb 11 60 ntext
3 bbb 11 60 ntext
4 ccccc 11 60 ntext
5 ddd 12 60 ntext
6 eeee 12 60 ntext(6 行受影响)*/
insert into a values(1 , 'aaa' , 11 , 60)
insert into a values(2 , 'bbb' , 11 , 60)
insert into a values(3 , 'bbb' , 11 , 60)
insert into a values(4 , 'ccc' , 11 , 60)
insert into a values(5 , 'ddd' , 12 , 60)
insert into a values(6 , 'eee' , 12 , 60)
create table B(folderid int, userid int, [read] int, del int)
insert into B values(60 , 11 , 1 , 1 )
insert into B values(60 , 12 , 1 , 1 )
insert into B values(60 , 13 , 1 , 1 )
go
select t1.* from a t1 ,
(
select folderid , userid from b m
where not exists
(
select 1 from (select folderid , userid from b where [read] <> 1) n
where n.folderid = m.folderid and n.userid = m.userid
)
) t2
where t1.folderid = t2.folderid and t1.userid = t2.useriddrop table a , b/*
fileid filename userid folderid
----------- ---------- ----------- -----------
1 aaa 11 60
2 bbb 11 60
3 bbb 11 60
4 ccc 11 60
5 ddd 12 60
6 eee 12 60(所影响的行数为 6 行)
*/
insert into a values(1 , 'aaa' , 11 , 60)
insert into a values(2 , 'bbb' , 11 , 60)
insert into a values(3 , 'bbb' , 11 , 60)
insert into a values(4 , 'ccc' , 11 , 60)
insert into a values(5 , 'ddd' , 12 , 60)
insert into a values(6 , 'eee' , 12 , 60)
create table B(folderid int, userid int, [read] int, del int)
insert into B values(60 , 11 , 1 , 1 )
insert into B values(60 , 12 , 1 , 1 )
insert into B values(60 , 13 , 1 , 1 )
go
--1
select a.* from a , b
where a.folderid = b.folderid and a.userid = b.userid and b.[read] = 1
/*
fileid filename userid folderid
----------- ---------- ----------- -----------
1 aaa 11 60
2 bbb 11 60
3 bbb 11 60
4 ccc 11 60
5 ddd 12 60
6 eee 12 60(所影响的行数为 6 行)
*/--2
select t1.* from a t1 ,
(
select folderid , userid from b m
where not exists
(
select 1 from (select folderid , userid from b where [read] <> 1) n
where n.folderid = m.folderid and n.userid = m.userid
)
) t2
where t1.folderid = t2.folderid and t1.userid = t2.userid
/*
fileid filename userid folderid
----------- ---------- ----------- -----------
1 aaa 11 60
2 bbb 11 60
3 bbb 11 60
4 ccc 11 60
5 ddd 12 60
6 eee 12 60(所影响的行数为 6 行)
*/drop table a , b