时间戳

首页 > 解决方案 > TSQL：如何合并多个复杂的选择？

sql - TSQL：如何合并多个复杂的选择？

问题描述

TSQL（我在 Dynamics 365 中使用它）

我有两个select疑问。他们都使用递归：

-- first selection
with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id)
select Id, pathstr
from tree  with (nolock) order by pathstr;

-- second selection
with tree2 (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr) as nvarchar(160)), 
    tree2.targetId, tree2.targetName
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId = T.BusinessUnitId)
select top(1) Id, pathstr
from tree2 with (nolock) order by len(pathstr) desc;

另外，这两个查询都有效。但我需要将他们的结果合二为一。我该怎么做？

这是我的尝试，但它不起作用：

-- first selection
with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id)
select Id, pathstr
from tree  with (nolock) order by pathstr
union all
-- second selection
with tree2 (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr) as nvarchar(160)), 
    tree2.targetId, tree2.targetName
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId = T.BusinessUnitId)
select top(1) Id, pathstr
from tree2 with (nolock) order by len(pathstr) desc

我得到错误：

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'union'.
Msg 319, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

更新：

谢谢你们！

有用：

with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id),

tree2 (Name2, Id2, ParentId2, pathstr2, targetId2, targetName2)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr2) as nvarchar(160)), 
    tree2.targetId2, tree2.targetName2
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId2 = T.BusinessUnitId)

select Id, pathstr
from tree  with (nolock) --order by pathstr
UNION ALL
select top(1) tree2.Id2, tree2.pathstr2
from tree2 with (nolock) --order by len(tree2.pathstr2) desc;

但是第二个选择包含的记录与我预期的不同，因为我发表了评论order by。我该如何解决？

更新2：

我尝试解决oredr by问题：

with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id),

tree2 (Name2, Id2, ParentId2, pathstr2, targetId2, targetName2)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr2) as nvarchar(160)), 
    tree2.targetId2, tree2.targetName2
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId2 = T.BusinessUnitId)

select Id, pathstr
from tree  with (nolock)
UNION ALL
-- subquery:
(select top(1) tree2.Id2, tree2.pathstr2
from tree2 with (nolock) order by len(pathstr2) desc) -- Error: Incorrect syntax near the keyword 'order'.
order by pathstr

标签： sqlsql-servertsql

解决方案

您试图WITH在现有语句中开始第二个，这不是 CTE 的工作方式。您只需声明WITH一次：

WITH tree ([Name], Id, ParentId, pathstr, targetId, targetName) as (
    SELECT [Name],
           BusinessUnitId,
           ParentBusinessUnitId,
           [Name], --Name again?
           BusinessUnitId AS targetId,
           [Name] --Name again again?
    FROM BusinessUnit WITH (NOLOCK) --Why NOLOCK?
    WHERE BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
    UNION ALL
    SELECT T.Name,
           T.BusinessUnitId,
           T.ParentBusinessUnitId,
           CAST((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
           tree.targetId,
           tree.targetName
    FROM BusinessUnit AS T  WITH (NOLOCK) --T for Business name, not for Tree?
        INNER JOIN tree ON T.ParentBusinessUnitId = tree.Id),
tree2 (Name, Id, ParentId, pathstr, targetId, targetName) AS(
    SELECT [Name],
           BusinessUnitId,
           ParentBusinessUnitId,
           [Name], --Name again?
           BusinessUnitId AS targetId,
           [Name] --Name again again?
    FROM BusinessUnit WITH (NOLOCK) --Why NOLOCK?
    WHERE BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
    UNION ALL
   SELECT T.Name,
           T.BusinessUnitId,
           T.ParentBusinessUnitId,
           CAST((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
           tree.targetId,
           tree.targetName
    FROM BusinessUnit AS T WITH (NOLOCK) --T for Business name, not for Tree?
        INNER JOIN tree ON T.ParentBusinessUnitId = tree.Id)
SELECT Id, pathstr
FROM tree WITH (NOLOCK)
UNION ALL
SELECT Id, pathstr
FROM(SELECT TOP(1) Id, pathstr
     FROM tree2 WITH (NOLOCK)
     ORDER BY LEN(pathstr) DESC);

不过，我还没有在这里修复其他错误，例如，Name在您的 CTE 中声明了 3 次）

编辑：在 SQL 中添加了一些注释。

推荐阅读