数据查询

[lishery]

用户表(id,zw)
id zw
001 1,2,5
002 2,4
资源表(id,qx)
id qx
1 1,3
2 2,3
3 1,5

一个用户可以有多个职位，每个资源的查看权限也有多个职位。
用户只要有查询权限中的一个职位即可查看资源，请问如何查出每个用户能够查询的资源？

[ojuju10]

Create table aa(Id varchar(10),zw varchar(10))
insert into aa select '001','1,2,5'
insert into aa select '002','2,4'

create table bb(Id int,qx varchar(10))
insert into bb select 1,'1,3'
insert into bb select 2,'2,3'
insert into bb select 3,'1,5'

select id=Identity(int,1,1) into # from sysobjects

select a.id,b.id as qx from  
(
select a.id,zw=substring(a.zw,b.id,charindex(',',a.zw+',',b.id)-b.id) from aa a,# b
where substring(','+a.zw,b.id,1)=','
) a, bb b
where charindex(','+a.zw+',',','+b.qx+',')>0
group by a.id,b.id

/*
id         qx
---------- -----------
001        1
001        2
001        3
002        2

(4 行受影响)
*/

[dobear_0922]

建议修改表结构

[子陌红尘]

create table users(id varchar(8),zw varchar(20)) 
insert into users values('001','1,2,5') 
insert into users values('002','2,4'  ) 

create table sources(id int,qx varchar(20)) 
insert into sources values(1,'1,3') 
insert into sources values(2,'2,3') 
insert into sources values(3,'1,5') 
go

create function f_include(@zw varchar(20),@qx varchar(20))
returns bit
as
begin
    declare @str varchar(10)
    
    select @zw=@zw+',',@qx=','+@qx+','
    
    while charindex(',',@zw)>0
    begin
        select @str=','+left(@zw,charindex(',',@zw)),
               @zw =stuff(@zw,1,charindex(',',@zw),'')
        
        if charindex(@str,@qx)>0
            return 1
    end    
    
    return 0
end
go

select u.*,s.* from users u,sources s where dbo.f_include(u.zw,s.qx)=1
/*
id       zw                   id          qx                   
-------- -------------------- ----------- -------------------- 
001      1,2,5                1           1,3
001      1,2,5                2           2,3
002      2,4                  2           2,3
001      1,2,5                3           1,5
*/
go

drop function f_include
drop table users,sources
go

[pt1314917]

用户表(id,zw)
id zw
001 1,2,5
002 2,4
资源表(id,qx)
id qx
1 1,3
2 2,3
3 1,5
----------------
这两个表怎么对应？

[jhwcd]

[Quote=引用 3 楼 libin_ftsafe 的回复:]
SQL codecreate table users(id varchar(8),zw varchar(20))
insert into users values('001','1,2,5')
insert into users values('002','2,4' )

create table sources(id int,qx varchar(20))
insert into sources values(1,'1,3')
insert into sources values(2,'2,3')
insert into sources values(3,'1,5')
go

create function f_include(@zw varchar(20),@qx varchar(20))
returns bit
as
begin
…
[/Quote]

[fengyunxnc]

最少要有四个表.
用户基本表.A（UID,Uname）
用户职位扩展表B(ZHID,UID,ZhiWeiNingCheng).

资源表C(ZID,ZNAME).
资源职位影射表D(ZID,UID)．

Select distinct(C.ZID),C.ZNAME,A.UID from a left join b on a.uid=b.uid left join D on c.UID=D.UID left join C on C.ZID=D.ZID