我想要左右用户的二进制和我的所有cid左侧和右侧 [英] i want total left and right user its binary and all my cid left side and right side
本文介绍了我想要左右用户的二进制和我的所有cid左侧和右侧的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
insert into binaryuser 值(2015-02-02,L, 4 , 5 , 4 )
insert into binaryuser < span class =code-keyword> values (2015-02-02,R, 5 , 8 , 4 )
insert 进入 binaryuser 值(2015-02-02,R, 4 , 11 , 4 )
insert into binaryuser values (2015-02-02,L, 5 , 10 , 4 )
insert < span class =code-keyword> into binaryuser values (2015-12-12,L, 8 , 9 , 4 )
将插入 binaryuser 值(2015-08-21,R, 8 , 12 , 4 )
insert into binaryuser values (2015-08-21 ,R, 12 , 13 , 4 )
insert into binaryuser values (2015-08- 21,R, 9 , 14 , 4 )
insert into binaryuser values (2015-08-21,M, 9 , 15 , 4 )
插入 进入 binaryuser values (2015-08-21,L, 10 , 16 , 4 )
insert into binaryuser values92015-08-21,M, 4 , 17 , 4 )
CREATE T. ABLE [dbo]。[binaryUser](
[id] [ int ] IDENTITY ( 1 , 1 )不 NULL ,
[joinedDate] [ date ] NULL ,
[placement] [ varchar ]( 100 ) NULL ,
[pId] [ int ] NOT NULL ,
[cId] [ int ] NOT NULL ,
[referBy] [ int ] NOT NULL )
CREATE TABLE [dbo]。[Users](
[userId] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[useName] [ varchar ]( 500 ) NULL ,
[mobileNo] [ char ]( 15 ) NULL ,
[dateOfBirth] [ date ] NULL ,
[地址] [ nvarchar ](max) NULL ,
[性别] [ varchar ]( 20 ) NULL ,
[matrialStatus] [ varchar ]( 30 ) NULL ,
[email] [ nvarchar ]( 100 ) NULL ,
[panNo] [ varchar ]( 50 ) NULL ,
[nomineeName] [ varchar ]( 200 ) NULL ,
[nomineeRelation] [ nvarchar ]( 100 ) NULL ,
[cityId] [ int ] < span class =code-keyword> NOT NULL )
解决方案
声明 @ insertCount as tinyint
create 表 #Temp
(
cid int ,
展示位置 char ( 1 ),
TotalLeft int ,
TotalRight int
)
insert into #Temp
(cid)
值( @ runTimeId )
声明 @ insertCount as tinyint ,
@ TotalLef varchar (max)
set @ insertCount = < span class =code-digit> 1 - 首次初始化值
while ( @ insertCount != 0 )
开始
INSERT INTO #Temp(cid,Placement)
选择 cId, placement 来自 binaryUser 其中 placement!= ' M' 和 EXISTS
( SELECT cid,placement FROM #Temp
WHERE binaryUser.pid =#Temp.cid)
AND NOT EXISTS
( SELECT cid,placement FROM #Temp
WHERE binaryUser.cid =#Temp.cid);
SET @ insertCount = @@ ROWCOUNT 跨度>;
end
select #Temp.cid,#Temp。 Placement,#Temp.TotalLeft,#Temp.TotalRight,Users.useName,Users.mobileNo from #Temp inner join 用户 Users.userId =#Temp.cid 其中#Temp.cid!= @ runTimeId
delete #Temp 其中 cid = @ runTimeId
drop 表 #Temp
听到TotalLeftChild而且totalRight为空列我想要数左右用户详情帮助我PLZ
insert into binaryuser values (2015-02-02 ,L ,4 ,5 ,4)
insert into binaryuser values(2015-02-02 ,R ,5 ,8 ,4)
insert into binaryuser values(2015-02-02 ,R ,4 ,11 ,4)
insert into binaryuser values(2015-02-02 ,L ,5 ,10 ,4)
insert into binaryuser values (2015-12-12 ,L, 8 ,9 ,4)
insert into binaryuser values(2015-08-21 ,R ,8 ,12 ,4)
insert into binaryuser values(2015-08-21 ,R ,12 ,13 ,4)
insert into binaryuser values(2015-08-21 ,R ,9 ,14 ,4)
insert into binaryuser values(2015-08-21 ,M ,9 ,15 ,4)
insert into binaryuser values(2015-08-21 ,L ,10 ,16 ,4)
insert into binaryuser values92015-08-21 ,M ,4 ,17 ,4)
CREATE TABLE [dbo].[binaryUser](
[id] [int] IDENTITY(1,1) NOT NULL,
[joiningDate] [date] NULL,
[placement] [varchar](100) NULL,
[pId] [int] NOT NULL,
[cId] [int] NOT NULL,
[referBy] [int] NOT NULL)
CREATE TABLE [dbo].[Users](
[userId] [int] IDENTITY(1,1) NOT NULL,
[useName] [varchar](500) NULL,
[mobileNo] [char](15) NULL,
[dateOfBirth] [date] NULL,
[address] [nvarchar](max) NULL,
[gender] [varchar](20) NULL,
[matrialStatus] [varchar](30) NULL,
[email] [nvarchar](100) NULL,
[panNo] [varchar](50) NULL,
[nomineeName] [varchar](200) NULL,
[nomineeRelation] [nvarchar](100) NULL,
[cityId] [int] NOT NULL)
解决方案
declare @insertCount as tinyint create table #Temp ( cid int, Placement char (1), TotalLeft int, TotalRight int ) insert into #Temp (cid) values (@runTimeId) declare @insertCount as tinyint, @TotalLef varchar(max) set @insertCount = 1 --initialize value for first time while(@insertCount != 0) begin INSERT INTO #Temp(cid,Placement) select cId,placement from binaryUser where placement !='M' and EXISTS (SELECT cid,placement FROM #Temp WHERE binaryUser.pid = #Temp.cid) AND NOT EXISTS (SELECT cid,placement FROM #Temp WHERE binaryUser.cid = #Temp.cid); SET @insertCount = @@ROWCOUNT; end select #Temp.cid ,#Temp.Placement,#Temp.TotalLeft,#Temp.TotalRight,Users.useName,Users.mobileNo from #Temp inner join Users on Users.userId=#Temp.cid where #Temp.cid !=@runTimeId delete #Temp where cid=@runTimeId drop table #Temp
hear TotalLeftChild And totalRight is null column here i want count left and right user details help me plz
这篇关于我想要左右用户的二进制和我的所有cid左侧和右侧的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文