SQL分层查询-多层，只关心一层
SQL分层查询-多层，只关心一层
提问于 2015-07-09 02:17:27
我有一个表，其中列出了工单和工单信息。每个作业至少有一个工单，大多数作业都有多个工单。有些工单可以有子工单，子工单也可以有子工单。这为我们提供了多层层次结构。
对于我正在构建的报表，我希望将所有工作订单按照它们所属的第二级工作订单进行分组。我把这个叫做SectorWO。
我已经编写了以下查询，它几乎实现了我想要的结果，但它不包括第二级工作订单本身。我曾尝试在SQL Fiddle上构建示例数据，但我的查询不会在那里运行(尽管它确实在生产SQL Server 2012上运行)。在填充临时表的语句被终止后查询临时表看起来有问题吗？
无论如何，这是我在SQL文件系统上构建的东西，希望它足以演示……
模式：
CREATE TABLE WOMaster (WorkOrder VARCHAR(12), ParentWorkOrder VARCHAR(12), Job VARCHAR(12))
INSERT INTO WOMaster (WorkOrder, ParentWorkOrder, Job)
VALUES (1,NULL,101),(2,1,101),(3,2,101),(4,3,101),(5,4,101),(6,4,101),(7,4,101),(8,4,101),(9,2,101),(10,2,101),
(11,2,101),(12,1,101),(13,12,101),(14,12,101),(15,12,101),(16,2,101),(17,1,101),(18,17,101),(19,17,101),(20,1,101),
(21,1,101),(22,21,101),(23,22,101),(24,22,101),(25,24,101),(26,24,101),(27,1,101),(28,27,101),(29,28,101),
(30,1,101),(31,30,101),(32,31,101),(33,32,101),(34,1,101),(35,34,101),(36,35,101),(37,36,101);查询：
DECLARE @pJob VARCHAR(Max)
SET @pJob = '101'
DECLARE @pWorkOrder VARCHAR(6)
SET @pWorkOrder = '1'
DECLARE @WOList1 TABLE (
Job VARCHAR(12)
,WorkOrder VARCHAR(12)
,ParentWorkOrder VARCHAR(12)
)
DECLARE @WOList2 TABLE (
Job VARCHAR(12)
,WorkOrder VARCHAR(12)
,SectorWO VARCHAR(12)
)
INSERT INTO @WOList1 (
Job
,WorkOrder
,ParentWorkOrder
)
SELECT Job
,WorkOrder
,ParentWorkOrder
FROM WOMaster
WHERE Job = @pJob
AND ParentWorkorder <> WorkOrder;
WITH cte
AS (
SELECT WO1.WorkOrder
,WO1.ParentWorkOrder
FROM @WOList1 WO1
UNION ALL
SELECT c.WorkOrder
,WO1.ParentWorkOrder
FROM @WOList1 WO1
INNER JOIN cte c ON c.ParentWorkOrder = WO1.WorkOrder
)
INSERT INTO @WOList2 (
Job
,WorkOrder
,SectorWO
)
SELECT WO1.Job
,C.WorkOrder
,C.ParentWorkOrder
FROM CTE C
LEFT JOIN @WOList1 WO1 ON C.WorkOrder = WO1.WorkOrder
WHERE C.ParentWorkOrder IN (
SELECT WorkOrder
FROM @WOList1
WHERE ParentWorkOrder = @pWorkOrder
)预期输出：
Job WorkOrder SectorWO
101 2 2
101 3 2
101 4 2
101 5 2
101 6 2
101 7 2
101 8 2
101 9 2
101 10 2
101 11 2
101 16 2
101 12 12
101 13 12
101 14 12
101 15 12
101 17 17
101 18 17
101 19 17
101 20 20
101 21 21
101 22 21
101 23 21
101 24 21
101 25 21
101 26 21
101 27 27
101 28 27
101 29 27
101 30 30
101 31 30
101 32 30
101 33 30
101 34 34
101 35 34
101 36 34
101 37 34回答 1
Stack Overflow用户
发布于 2015-07-09 02:31:37
您还没有举例说明您希望输出是什么样子。所以这是我最好的尝试。我觉得你可能把事情搞得太复杂了。
SELECT subwo.job, subwo.workorder SubWO, secondsubwo.workorder SecondSubWO
FROM WOMaster
LEFT JOIN WOMaster SubWO ON Subwo.parentworkorder = womaster.workorder
LEFT JOIN WOMaster SecondSubWo ON SecondSubWo.parentworkorder = Subwo.workorder
WHERE WOMaster.parentworkorder IS NULL
ORDER BY cast(subwo.workorder AS INT), cast(secondsubWO.workorder AS INT)现在你已经弄清楚了你的输出。我认为你应该使用递归公用表表达式来获得你想要的东西。
;
with woList as
(select toplevel.workorder, toplevel.job, toplevel.parentworkorder
, 1 as WOLevel
From womaster toplevel
UNION ALL
SELECT wo.workorder, wo.job, wo.parentworkorder
, wolist.wolevel+1 as wolevel
FROM womaster wo
INNER JOIN wolist on wo.parentworkorder=wolist.workorder
WHERE wo.parentworkorder is not null)
select
WoMaster.job, WoMaster.workorder, sector.workorder SectorWO
from womaster womaster
inner join wolist sector on sector.workorder=womaster.parentworkorder
where wolevel=2
UNION ALL
Select sector2.job, sector2.workorder, sector2.workorder
FROM wolist sector2
where wolevel=2
order by cast(womaster.job as int), cast(sector.workorder as int), cast(womaster.workorder as int)Pinal Dave explains this really well.
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31300560
复制相关文章
相似问题
腾讯云开发者
