有三个表,user, camera, uc,如何写一个sql语句,使得能通过uc表现是相对用户所能查看的camera?camera
id cameraname
1 cam1
2 cam2
3 cam3
4 cam4
5 cam5
6 cam6
7 cam7
8 cam8user
id username
1 bryan
2 tonyuc
id uid cid
1 2 3
2 2 4
3 1 1
4 1 5
5 1 6
id cameraname
1 cam1
2 cam2
3 cam3
4 cam4
5 cam5
6 cam6
7 cam7
8 cam8user
id username
1 bryan
2 tonyuc
id uid cid
1 2 3
2 2 4
3 1 1
4 1 5
5 1 6
a.id,
b.username,
c.cameraname
from uc as a
join [user] as b
on a.uid=b.id
join [camera] as c
on a.cid=c.id
a.id,
b.username,
c.cameraname
from uc as a,
[user] as b,
[camera] as c
where
a.uid=b.id
and
a.cid=c.id
c.id,
b.username,
a.cameraname
from
uc c
left join [user] b on c.uid=b.id
left join camera a on c.cid=a.id
On A.uid=B.id inner join camera C on A.cid=c.id
where a.id=c.cid and b.id=c.uid and b.username='你要查找的用户'--如tony
select a.* from camera a,user b,uc c
where a.id=c.cid and b.id=c.uid and b.username='tony'
declare @a varchar(8)
select @a='Tony' --自己定义用户
select
a.id,
b.username,
c.cameraname
from uc as a,
[user] as b,
[camera] as c
where
a.uid=b.id and a.cid=c.id and b.username=@a
select camera.* from camera
inner join UC on UC.cid = camera.id
inner join [user] on [user].id = UC.uid
where [user].[id]=''
select camera.* from camera
inner join UC on UC.cid = camera.id
inner join [user] on [user].id = UC.uid
where [user].[username]= ?
--orselect c.* from
camera c,uc uc,user u
where u.id = uc.uid and uc.cid = c.cid
and user.username = ?