使用CTE进行递归选择时出现错误 - "UNION运算符必须具有相等数量的表达式"

Question

目前我有一张表格tblLocation，包含列ID，Location，PartOfID。

这个表格是递归连接的：PartOfID -> ID

我的目标是要有以下选择输出：

> France > Paris > AnyCity >

解释：AnyCity位于巴黎，巴黎位于法国。

我目前找到的解决方案是：

; with q as (
select ID,Location,PartOf_LOC_id from tblLocatie t
where t.ID = 1 -- 1 represents an example
union all
select t.Location + '>' from tblLocation t
inner join q parent on parent.ID = t.LOC_PartOf_ID
)
select * from q

很不幸，我遇到了以下错误：

使用 UNION、INTERSECT 或 EXCEPT 操作符组合的所有查询必须在其目标列表中具有相等数量的表达式。

如果您有任何想法可以帮助我解决输出问题，那将是非常好的。

- user2871811

---

错误信息有什么不清楚的地方吗？ - user330315

Answer 1

这里存在问题：

--This result set has 3 columns
select LOC_id,LOC_locatie,LOC_deelVan_LOC_id from tblLocatie t
where t.LOC_id = 1 -- 1 represents an example

union all

--This result set has 1 columns   
select t.LOC_locatie + '>' from tblLocatie t
inner join q parent on parent.LOC_id = t.LOC_deelVan_LOC_id

为了使用 union 或 union all，所有结果集中的列数和列类型必须相同。 我猜你应该在第二个结果集中添加列 LOC_deelVan_LOC_id 。

- Yosi Dahari

---

是的，但即使我将它们相等，也会出现以下错误：递归查询“q”中列“ID”的锚点和递归部分之间的类型不匹配。 - user2871811

Yosi，实际上我认为第二个select语句只有一个列"ll select t.Location + '>'"。但是除此之外，我同意你的答案，即两个select语句必须具有相同数量的列。 - asantaballa

@asantaballa - 你是对的。我已经纠正了。@user2871811，请看问题的编辑，类型也应该匹配。 - Yosi Dahari

Answer 2

第二个“结果集”只有一列，但它应该有3列才能与第一个“结果集”相匹配（在使用“UNION”时必须匹配列）。尝试将“ID”作为第一列添加到你的“结果集”中，并将“PartOf_LOC_id”添加到你的“结果集”中，这样你就可以执行“UNION”操作。

;
WITH    q AS ( SELECT   ID ,
                    Location ,
                    PartOf_LOC_id
           FROM     tblLocation t
           WHERE    t.ID = 1 -- 1 represents an example
           UNION ALL
           SELECT   t.ID ,
                    parent.Location + '>' + t.Location ,
                    t.PartOf_LOC_id
           FROM     tblLocation t
                    INNER JOIN q parent ON parent.ID = t.LOC_PartOf_ID
         )
SELECT  *
FROM    q

- ARA

Answer 3

两个部分的联合操作中列的数量必须匹配。

为了构建完整的路径，您需要“聚合”Location列的所有值。仍然需要在CTE中选择id和其他列，以便能够正确地进行连接。通过在外部选择中不选择它们，可以将它们“去除”：

with q as 
(
   select ID, PartOf_LOC_id, Location, ' > ' + Location as path
   from tblLocation 
   where ID = 1 

   union all

   select child.ID, child.PartOf_LOC_id, Location, parent.path + ' > ' + child.Location 
   from tblLocation child
     join q parent on parent.ID = t.LOC_PartOf_ID
)
select path
from q;

- user330315

Answer 4

虽然这是一篇旧帖子，但我分享另一个可用的示例。

"当使用 'UNION' 或 'UNION ALL' 时，列计数以及每个列的数据类型必须匹配"

让我们举个例子：

1：

在 SQL 中，如果我们写入 SELECT 'column1', 'column2'（注意要用引号指定名称），则结果集将显示带有两个标题的空列 - column1 和 column2。

2：我分享一个我遇到的简单实例。

我在 SQL 中有七列不同的数据类型，例如 uniqueidentifier、datetime、nvarchar。

我的任务是检索带有列标题的逗号分隔结果集。这样，当我将数据导出到 CSV 文件时，就会得到逗号分隔的行，第一行为标题，并包含相应的列名。

SELECT CONVERT(NVARCHAR(36), 'Event ID') + ', ' + 
'Last Name' + ', ' + 
'First Name' + ', ' + 
'Middle Name' + ', ' + 
CONVERT(NVARCHAR(36), 'Document Type') + ', ' + 
'Event Type' + ', ' + 
CONVERT(VARCHAR(23), 'Last Updated', 126)

UNION ALL

SELECT CONVERT(NVARCHAR(36), inspectionid) + ', ' + 
       individuallastname + ', ' + 
       individualfirstname + ', ' + 
       individualmiddlename + ', ' +
       CONVERT(NVARCHAR(36), documenttype) + ', ' + 
       'I' + ', ' +
       CONVERT(VARCHAR(23), modifiedon, 126)
FROM Inspection

以上，列 'inspectionid' 和 'documenttype' 具有唯一标识符数据类型，因此应用了 CONVERT(NVARCHAR(36))。列 'modifiedon' 是日期时间类型，因此应用了 CONVERT(NVARCHAR(23), 'modifiedon', 126)。

与上述第一个 SELECT 查询相匹配的第二个 SELECT 查询与每列的数据类型相匹配。

- Binoy

Answer 5

您可以使用递归标量函数：-

set nocount on

create table location (
    id int,
    name varchar(50),
    parent int
)
insert into location values
    (1,'france',null),
    (2,'paris',1),
    (3,'belleville',2),
    (4,'lyon',1),
    (5,'vaise',4),
    (6,'united kingdom',null),
    (7,'england',6),
    (8,'manchester',7),
    (9,'fallowfield',8),
    (10,'withington',8)
go
create function dbo.breadcrumb(@child int)
returns varchar(1024)
as begin
    declare @returnValue varchar(1024)=''
    declare @parent int
    select @returnValue+=' > '+name,@parent=parent
    from location
    where id=@child
    if @parent is not null
        set @returnValue=dbo.breadcrumb(@parent)+@returnValue
    return @returnValue
end
go

declare @location int=1
while @location<=10 begin
    print dbo.breadcrumb(@location)+' >'
    set @location+=1
end

产生：-

 > france >
 > france > paris >
 > france > paris > belleville >
 > france > lyon >
 > france > lyon > vaise >
 > united kingdom >
 > united kingdom > england >
 > united kingdom > england > manchester >
 > united kingdom > england > manchester > fallowfield >
 > united kingdom > england > manchester > withington >

- dav1dsm1th