表结构:CREATE TABLE [dbo].[users] (
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[userAccount] [varchar] (20) NOT NULL ,
[userPSW] [varchar] (32) NOT NULL ,
[userRelay] [int] NOT NULL ,
[userStatus] [int] NOT NULL ,
[userSuperior] [int] NOT NULL , --用户上级ID
) ON [PRIMARY]
GOselect userSuperior,(select userAccount from users where userID=userSuperior) as superior,(select count(userID) from users where userSuperior=userID) as superiorNum from users
想得到用户的上级的用户名和下属个数,可是执行结果是NULL 和 0 (数据库中有记录的)
各位帮我看看有什么问题哈,怎么得到我想要的结果啊
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[userAccount] [varchar] (20) NOT NULL ,
[userPSW] [varchar] (32) NOT NULL ,
[userRelay] [int] NOT NULL ,
[userStatus] [int] NOT NULL ,
[userSuperior] [int] NOT NULL , --用户上级ID
) ON [PRIMARY]
GOselect userSuperior,(select userAccount from users where userID=userSuperior) as superior,(select count(userID) from users where userSuperior=userID) as superiorNum from users
想得到用户的上级的用户名和下属个数,可是执行结果是NULL 和 0 (数据库中有记录的)
各位帮我看看有什么问题哈,怎么得到我想要的结果啊
(select userAccount from users where [userID]=U.userSuperior) as superior,
(select count(userID) from users where userSuperior=U.[userID]) as superiorNum
from users U?
select userAccount from users where userID=A.userSuperior) as superior,
(select count(userID) from users where userSuperior=A.userID) as superiorNum
from users A
select userSuperior,
(select userAccount from users where userID=A.userSuperior) as superior,
(select count(userID) from users where userSuperior=A.userID) as superiorNum
from users A
userSuperior,
(select userAccount from users where [userID]=t.userSuperior) as superior,
(select count(userID) from users where userSuperior=t.[userID]) as superiorNum
from
users t
select * ,
(select userAccount from [users] where userID = t.userSuperior) [上级的用户名],
(select count(1) from [users] where userSuperior = t.userID) [下属个数]
from [users] t
select * ,
isnull((select userAccount from [users] where userID = t.userSuperior),'') [上级的用户名],
isnull((select count(1) from [users] where userSuperior = t.userID),0) [下属个数]
from [users] t