Ошибка - "UNION оператор должен иметь равное количество выражений" при использовании CTE для рекурсивного выбора

В этот момент у меня есть таблица 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, должны иметь равное количество выражений в своих целевых списках.

Если у вас есть идея, как я могу исправить свой вывод, было бы здорово.

Ответ 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 во второй результирующий набор

Ответ 2

Второй result set имеет только один столбец, но должен иметь 3 столбца, чтобы он удовлетворялся с первым result set

(столбцы должны совпадать, если вы используете 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

Ответ 3

Затем число столбцов должно совпадать между обеими частями объединения.

Чтобы построить полный путь, вам необходимо "заполнить" все значения столбца Location. Вам все равно нужно выбрать идентификатор и другие столбцы внутри CTE, чтобы иметь возможность правильно подключаться. Вы избавляетесь от них, просто не выбирая их во внешнем выборе:

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;

Ответ 4

Вы можете использовать рекурсивную скалярную функцию: -

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 [email protected]
    if @parent is not null
        set @returnValue=dbo.breadcrumb(@parent)[email protected]
    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 >