错误 - “UNION 运算符必须具有相同数量的表达式"；使用 CTE 进行递归选择时 [英] Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection

问题描述

此时我有一个表 tblLocation，其中包含 ID、Location、PartOfID 列.

At this moment I have a table tblLocation with columns ID, Location, PartOfID.

表与自身递归连接:PartOfID ->身份证

我的目标是选择输出如下:

My goal is to have a select output as followed:

> France > Paris > AnyCity >

说明:AnyCity位于巴黎，巴黎位于法国.

Explanation: AnyCity is located in Paris, Paris is located in France.

我到现在为止找到的解决方案是这样的:

My solution that I found until now was this:

; 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

不幸的是，我收到以下错误:

Unfortunately I get the following error:

使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询在其目标列表中必须具有相同数量的表达式.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

如果您知道我如何修复我的输出，那就太好了.

If you have any idea how I could fix my output it would be great.

推荐答案

问题出在这里:

--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 列数和它们的类型应该在所有结果集中相同.

In order to use union or union all number of columns and their types should be identical cross all result sets.

我想您应该将 LOC_deelVan_LOC_id 列添加到您的第二个结果集中

I guess you should just add the column LOC_deelVan_LOC_id to your second result set

这篇关于错误 - “UNION 运算符必须具有相同数量的表达式"；使用 CTE 进行递归选择时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！