SQL 错误 - 使用 CTE 进行递归选择时“UNION 运算符必须具有相同数量的表达式”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20299390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection
提问by user2871811
At this moment I have a table tblLocationwith columns ID, Location, PartOfID.
此时我有一个tblLocation带有列的表ID, Location, PartOfID。
The table is recursively connected to itself: PartOfID -> ID
该表递归地连接到自身： PartOfID -> ID
My goal is to have a select output as followed:
我的目标是有一个选择输出如下：
> France > Paris > AnyCity >
Explanation: AnyCity is located in Paris, Paris is located in France.
说明：AnyCity位于巴黎，巴黎位于法国。
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:
不幸的是，我收到以下错误：
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询在其目标列表中必须具有相同数量的表达式。
If you have any idea how I could fix my output it would be great.
如果您知道我如何修复我的输出，那就太好了。
回答by Yosi Dahari
The problem lays here:
问题出在这里：
--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
In order to use unionor union allnumber of columns and their typesshould be identical cross all result sets.
为了使用union或union all列数和它们的类型在所有结果集上应该是相同的。
I guess you should just add the column LOC_deelVan_LOC_idto your second result set
我想您应该将该列添加LOC_deelVan_LOC_id到您的第二个结果集中
回答by ARA
The second result sethave only one column but it should have 3 columns for it to be contented to the first result set
第二个result set只有一列，但它应该有 3 列才能满足第一列result set
(columns must match when you use UNION)
（使用时列必须匹配UNION）
Try to add IDas first column and PartOf_LOC_idto your result set, so you can do the UNION.
尝试添加ID为第一列并添加PartOf_LOC_id到您的result set，以便您可以执行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
回答by a_horse_with_no_name
Then number of columns must match between both parts of the union.
然后，联合的两个部分之间的列数必须匹配。
In order to build the full path, you need to "aggregate" all values of the Locationcolumn. You still need to select the id and other columns inside the CTE in order to be able to join properly. You get "rid" of them by simply not selecting them in the outer select:
为了构建完整路径，您需要“聚合”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;
回答by Binoy
Although this an old post, I am sharing another working example.
虽然这是一篇旧帖子，但我正在分享另一个工作示例。
"COLUMN COUNT AS WELL AS EACH COLUMN DATATYPE MUST MATCH WHEN 'UNION' OR 'UNION ALL' IS USED"
“当使用‘联合’或‘联合所有’时，列计数以及每个列数据类型必须匹配”
Let us take an example:
让我们举个例子：
1:
1：
In SQL if we write - SELECT 'column1', 'column2' (NOTE: remember to specify names in quotes) In a result set, it will display empty columns with two headers - column1 and column2
在 SQL 中，如果我们写 - SELECT 'column1', 'column2'（注意：记住在引号中指定名称）在结果集中，它将显示带有两个标题的空列 - column1 和 column2
2: I share one simple instance I came across.
2：我分享一个我遇到的简单例子。
I had seven columns with few different datatypes in SQL. I.e. uniqueidentifier, datetime, nvarchar
我在 SQL 中有七列，其中包含几种不同的数据类型。即唯一标识符、日期时间、nvarchar
My task was to retrieve comma separated result set with column header. So that when I export the data to CSV I have comma separated rows with first row as header and has respective column names.
我的任务是检索带有列标题的逗号分隔结果集。因此，当我将数据导出到 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
Above, columns 'inspectionid' & 'documenttype' has uniqueidentiferdatatype and so applied CONVERT(NVARCHAR(36)). column 'modifiedon' is datetime and so applied CONVERT(NVARCHAR(23), 'modifiedon', 126).
上面，列 'inspectionid' 和 'documenttype' 具有uniqueidentifer数据类型等应用CONVERT(NVARCHAR(36))。列 'modifiedon' 是日期时间，因此适用CONVERT(NVARCHAR(23), 'modifiedon', 126)。
Parallel to above 2nd SELECTquery matched 1st SELECTquery as per datatype of each column.
与上面的第二个SELECT查询并行，SELECT根据每列的数据类型匹配第一个查询。
回答by dav1dsm1th
You could use a recursive scalar function:-
您可以使用递归标量函数：-
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
produces:-
产生：-
> 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 >
