SQL连接和连接行

小编典典

SQL连接和连接行

sql

我在两个表之间有多对多关系。

表God_Restaurants包含我的餐厅。

表God_RestaurantKat包含不同的类别。

表God_RestKatReference包含两列，每列分别包含两个表的ID。

以下陈述是我能想到的，但没有给我我想要的输出。

DECLARE @Names VARCHAR(8000) 
SELECT DISTINCT R.RestaurantID as Restaurantid, 
                R.RestaurantName as Restaurantname, 
                K.RestaurantKatName as RestKatName 
FROM God_Restaurants R 
LEFT JOIN God_RestKatReference as GodR ON R.RestaurantId = Godr.RestaurantId 
LEFT JOIN God_RestaurantKat as K ON GodR.RestaurantKatId = K.RestaurantKatId 
WHERE R.RestaurantPostal = 7800

我希望输出是有关餐厅的信息，并在最后一列中是类别的连接行。

阅读 290

2021-04-14

共1个答案

小编典典

要串联值，可以使用for xml path('')。xml路径解决方案有误，应使用value和type特殊字符。

declare @Temp table (id int, Name nvarchar(max))
declare @date datetime
declare @i int

insert into @Temp
select 1, 'asasd' union all
select 1, 'sdsdf' union all
select 2, 'asdad' union all
select 3, 'asd<a?>&sdasasd' union all
select 3, 'fdgdfg'

select @i = 1
while @i < 9
begin
    insert into @Temp
    select id, Name from @Temp

    select @i = @i + 1
end

select count(*) from @Temp

select @date = getdate()

select
    A.id,
    stuff((select ', ' + TT.Name from @Temp as TT where TT.id = A.id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Names
from @Temp as A
group by A.id

select datediff(ms, @date, getdate())

select @date = getdate()

select distinct
    A.id,
    stuff((select ', ' + TT.Name from @Temp as TT where TT.id = A.id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Names
from @Temp as A

select datediff(ms, @date, getdate())

您也可以使用变量解决方案

declare @temp nvarchar(max)

select @temp = isnull(@temp + ', ', '') + str
from (select '1' as str union select '2' as str union select '3' as str) as A

select @temp

2021-04-14