6,129
社区成员
发帖
与我相关
我的任务
分享sqlserver中with..as..的使用
Create table Storage_Depository
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
然后往该表插入演示数据：
insert into Storage_Depository(DID,DName,PID)
select 'A','A仓库',null
union all
select 'A-1','A-1仓库','A'
union all
select 'A-2','A-2仓库','A'
union all
select 'A-1-1','A-1-1仓库','A-1'
union all
select 'B','B仓库',null
用
with w_Storage_Depository as
(
select DID,DName,PID,0 as lev from Storage_Depository where DID='A'
union all
select A.DID,A.DName,A.PID,B.lev + 1 from Storage_Depository A,w_Storage_Depository B where A.PID=B.DID
)
select * from w_Storage_Depository
得出的情况如下
DID DName PID lev
A A仓库 NULL 0
A-1 A-1仓库 A 1
A-2 A-2仓库 A 1
A-1-1 A-1-1仓库 A-1 2
我实际需要的结果如下
DID DName PID lev
A A仓库 NULL 0
A-1 A-1仓库 A 1
A-1-1 A-1-1仓库 A-1 2
A-2 A-2仓库 A 1
请问这个语句要怎么写？
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
然后往该表插入演示数据：
insert into Storage_Depository(DID,DName,PID)
select 'A','A仓库',null
union all
select 'A-1','A-1仓库','A'
union all
select 'A-2','A-2仓库','A'
union all
select 'A-1-1','A-1-1仓库','A-1'
union all
select 'B','B仓库',null
用
with w_Storage_Depository as
(
select DID,DName,PID,0 as lev from Storage_Depository where DID='A'
union all
select A.DID,A.DName,A.PID,B.lev + 1 from Storage_Depository A,w_Storage_Depository B where A.PID=B.DID
)
select * from w_Storage_Depository
得出的情况如下
DID DName PID lev
A A仓库 NULL 0
A-1 A-1仓库 A 1
A-2 A-2仓库 A 1
A-1-1 A-1-1仓库 A-1 2
我实际需要的结果如下
DID DName PID lev
A A仓库 NULL 0
A-1 A-1仓库 A 1
A-1-1 A-1-1仓库 A-1 2
A-2 A-2仓库 A 1
请问这个语句要怎么写？
...全文
请发表友善的回复…
发表回复
zhoudi007 2019-05-08
- 打赏
- 举报
首先感谢你的回复，测试是对的，但我还有哥问题，就是能不能再递归查询的时候，如果某一行没有子集，那么要给哥标识为N，怎么做
Dear SQL(燊) 2019-05-08
- 打赏
- 举报
Create table Storage_Depository
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
insert into Storage_Depository(DID,DName,PID)
select 'A','A仓库',null
union all
select 'A-1','A-1仓库','A'
union all
select 'A-2','A-2仓库','A'
union all
select 'A-1-1','A-1-1仓库','A-1'
union all
select 'B','B仓库',null
;
with w_Storage_Depository as
(
select DID,DName,PID,0 as lev,paths=cast(DID as nvarchar(1000))
from Storage_Depository where DID='A'
union all
select A.DID,A.DName,A.PID,B.lev + 1
,paths=cast(b.paths+'\'+a.DID as nvarchar(1000))
from Storage_Depository A,w_Storage_Depository B
where A.PID=B.DID
)
select *
from w_Storage_Depository
order by paths
/*
DID DName PID lev paths
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A A仓库 NULL 0 A
A-1 A-1仓库 A 1 A\A-1
A-1-1 A-1-1仓库 A-1 2 A\A-1\A-1-1
A-2 A-2仓库 A 1 A\A-2
(4 行受影响)
*/
Dear SQL(燊) 2019-05-08
- 打赏
- 举报
Create table Storage_Depository
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
insert into Storage_Depository(DID,DName,PID)
select 'A','A仓库',null
union all
select 'A-1','A-1仓库','A'
union all
select 'A-2','A-2仓库','A'
union all
select 'A-1-1','A-1-1仓库','A-1'
union all
select 'B','B仓库',null
;
with w_Storage_Depository as
(
select DID,DName,PID,0 as lev,paths=cast(DID as nvarchar(1000))
from Storage_Depository where DID='A'
union all
select A.DID,A.DName,A.PID,B.lev + 1
,paths=cast(b.paths+'\'+a.DID as nvarchar(1000))
from Storage_Depository A,w_Storage_Depository B
where A.PID=B.DID
)
select a.* ,mark=case when b.PID is not null then '' else 'N' end
from w_Storage_Depository a
left join w_Storage_Depository b on a.DID=b.PID
order by a.paths
/*
DID DName PID lev paths mark
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----
A A仓库 NULL 0 A
A A仓库 NULL 0 A
A-1 A-1仓库 A 1 A\A-1
A-1-1 A-1-1仓库 A-1 2 A\A-1\A-1-1 N
A-2 A-2仓库 A 1 A\A-2 N
(5 行受影响)
*/
SqlServer死锁com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 52) was deadlock
