查询四张表aspnet_Membership,aspnet_Users,Users,UserRoles。
1.不是用inner join四表连接查询
- declare @username nvarchar(100)
- declare @userpwd nvarchar(100)
- set @username='admin'
- set @userpwd='password'
- SELECT * FROM aspnet_Membership aM, aspnet_Users aU,Users U,UserRoles UR where aM.UserId=aU.UserId and
- aU.UserName=U.Username and U.UserID=UR.UserID
- and U.Username=@username and aM.password=@userpwd and UR.roleid=0
2.使用inner join四表连接查询
- declare @username nvarchar(100)
- declare @userpwd nvarchar(100)
- set @username='admin'
- set @userpwd='password'
- select * from (((aspnet_Membership INNER JOIN aspnet_users ON aspnet_Membership.UserId = aspnet_users.UserId)
- INNER JOIN Users ON aspnet_users.username=Users.username)INNER JOIN UserRoles ON Users.userid=UserRoles.userid)
- where Users.Username=@username and aspnet_Membership.password=@userpwd and UserRoles.roleid=0
使用上面的查询语句可以查询指定的username和password并且角色roleid=0的结果。
本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2010/03/15/2297159.html,如需转载请自行联系原作者