create table test_users
(
id int primary key identity(1,1) not null, --编号id 主键、自动标识列
username varchar (20) not null,
pid int null --推广ID ,0代表无推广人
)
insert into test_users values('n1',0)
insert into test_users values('n2',1)
insert into test_users values('n2',1)
insert into test_users values('n3',1)
insert into test_users values('n2',4)
insert into test_users values('n5',4)
create table test_orders
(
id int primary key identity(1,1) not null, --编号id 主键、自动标识列
uid int not null,
pirce float not null --编号id 主键、自动标识列
)
insert into test_orders values(1,100)
insert into test_orders values(3,999)
insert into test_orders values(3,11)
insert into test_orders values(5,51)
insert into test_orders values(4,45)
insert into test_orders values(6,12)--怎么查询每个用户推广了多少人。并且推广人充值了多少
go
create table [temp]([groupID] int,[foldName] varchar(10),[parentID] int)
insert [temp] select 1,'文件1',0
union all select 2,'文件2',0
union all select 3,'文件1-1',1
union all select 4,'文件2-1',2
union all select 5,'文件1-1-1',3
union all select 6,'文件2-1-1',4
--SQL查询如下:
;WITH t AS
(
SELECT groupID,foldName,parentID,
path=CAST(foldName AS VARCHAR(8000))
FROM Temp AS A
WHERE NOT EXISTS
(
SELECT *
FROM Temp
WHERE groupID=A.parentID
)
UNION ALL
SELECT
A.*,
B.path+'/'+A.foldName
FROM Temp AS A
JOIN t AS B
ON A.parentID=B.groupID
)
SELECT groupID,path AS foldname FROM t order by groupID
/*
groupID path
------- ---------------------------
1 文件1
2 文件2
3 文件1/文件1-1
4 文件2/文件2-1
5 文件1/文件1-1/文件1-1-1
6 文件2/文件2-1/文件2-1-1
(6 行受影响)
*/