学步园

查询四张表aspnet_Membership，aspnet_Users，Users，UserRoles。

1.不是用inner join四表连接查询

declare @username nvarchar(100)<br /> declare @userpwd nvarchar(100)<br /> set @username='admin'<br /> set @userpwd='password'<br /> SELECT * FROM aspnet_Membership aM, aspnet_Users aU,Users U,UserRoles UR where aM.UserId=aU.UserId and<br /> aU.UserName=U.Username and U.UserID=UR.UserID<br /> and U.Username=@username and aM.password=@userpwd and UR.roleid=0

2.使用inner join四表连接查询

declare @username nvarchar(100)<br /> declare @userpwd nvarchar(100)<br /> set @username='admin'<br /> set @userpwd='password'<br /> select * from (((aspnet_Membership INNER JOIN aspnet_users ON aspnet_Membership.UserId = aspnet_users.UserId)<br /> INNER JOIN Users ON aspnet_users.username=Users.username)INNER JOIN UserRoles ON Users.userid=UserRoles.userid)<br /> where Users.Username=@username and aspnet_Membership.password=@userpwd and UserRoles.roleid=0

使用上面的查询语句可以查询指定的username和password并且角色roleid=0的结果。