我已经实现了一个多表连接的sql语句,但现在又牵扯到了一个权限问题
我需要从另外一张表中把符合用户权限的数据从已有的那句sql中筛选出来,
这一新的条件该怎么实现呢?
看着这些sql都快头疼死了,大家不要笑话啊,谢谢~~已经实现的sqlselect distinct infant.infantId,infantName,isnull(t.teacherName, '') master,
city,country,address,infant.type,t.publicPhone,t.selfPhone,t.Email,t.QQ,postId,
isnull(d.teacherCount,0) teacherCount,infant.memo
from infant left join (Select a.* from teacher a where teacherName =
(Select top 1 teacherName from teacher where teacherWork = '园长' and infantId=a.infantId)) t
on infant.infantId=t.infantId
left join (select infantId , count(*) teacherCount from teacher_active b, active c
where b.activId = c.activId group by infantId) d on infant.infantId = d.infantId--需要连接的新条件:active表中userId为1的数据
我需要从另外一张表中把符合用户权限的数据从已有的那句sql中筛选出来,
这一新的条件该怎么实现呢?
看着这些sql都快头疼死了,大家不要笑话啊,谢谢~~已经实现的sqlselect distinct infant.infantId,infantName,isnull(t.teacherName, '') master,
city,country,address,infant.type,t.publicPhone,t.selfPhone,t.Email,t.QQ,postId,
isnull(d.teacherCount,0) teacherCount,infant.memo
from infant left join (Select a.* from teacher a where teacherName =
(Select top 1 teacherName from teacher where teacherWork = '园长' and infantId=a.infantId)) t
on infant.infantId=t.infantId
left join (select infantId , count(*) teacherCount from teacher_active b, active c
where b.activId = c.activId group by infantId) d on infant.infantId = d.infantId--需要连接的新条件:active表中userId为1的数据
try:
select distinct infant.infantId,infantName,isnull(t.teacherName, '') master,
city,country,address,infant.type,t.publicPhone,t.selfPhone,t.Email,t.QQ,postId,
isnull(d.teacherCount,0) teacherCount,infant.memo
from infant left join (Select a.* from teacher a where teacherName =
(Select top 1 teacherName from teacher where teacherWork = '园长' and infantId=a.infantId)) t
on infant.infantId=t.infantId
left join (select infantId , count(*) teacherCount from teacher_active b, active c
where b.activId = c.activId and c.Userid="1" group by infantId) d on infant.infantId = d.infantId
得到的结果就不对了,还是会把所有的权限都列出来,而不是指定的为“1”的权限了
再次感谢~~
create table active (
activId int primary key,
userId,int
)
insert into active
select 1,1 union all
select 2,2 union all
select 3,3 gocreate table infant (
infantId int primary key,
infantName varchar(30),
city varchar(10),
country varchar(10),
address varchar(50),
type int,
postId varchar(20),
memo varchar(50)
)
insert into infant
select 1,'小太阳','郑州市','中原区','XXX路',1,'450000','无' union all
select 2,'大苹果','郑州市','二七区','XXX路',1,'450000','无'gocreate table teacher(
teacherId int primary key,
infantId int, --infant表中主键
teacherName varchar(10),
teacherWork varchar(10),
publicPhone varchar(20),
selfPhone varchar(20),
Email varchar(30),
QQ varchar(20),
memo varchar(50)
)
insert into teacher
select 1,1,'张三','园长','031761234567','13113113131','[email protected]','123456','无' union all
select 2,2,'李四','教师','031762234567','13213113131','[email protected]','654321','无'gocreate table teacher_active (--teacher与active的连接表
id int primary key,
teacherId int, --关联teacher表
infantId int, --关联infant表
activId int --关联active表
)
insert into teacher_active
select 1,1,1,1 union all
select 2,2,2,2 drop table active
drop table teacher
drop table teacher
drop table teacher_active
现在我需要加一个限制权限的条件,即不同的用户登录只能看到自己权限范围内的数据
也就是说active中userId为1的用户只可以看到teacher_active中acitvId为1的数据不知描述清楚了没有