SQL查询，将多个视图合并为一个视图

Question

我试图用3或4个其他视图/表创建一个新视图。

表A:

title _id	homeTeam
1234	WSV
5678	SSV
7890	空
4321	SCC

您可以将表UNION ALL放在一起，然后使用字符串聚合

SELECT
t.title_id,
STRING_AGG(ISNULL(t.team, 'N/A'), ', ') WITHIN GROUP (ORDER BY t.ordering) AS team
FROM (
SELECT 
a.title_id,
a.homeTeam AS team,
1 AS ordering
FROM TableA a
UNION ALL
SELECT 
b.title_id,
b.awayTeam,
2
FROM TableB b
UNION ALL
SELECT 
c.title_id,
c.homeTeam,
3
FROM TableC c
) t
GROUP BY
t.title_id;

对于SQL Server 2016及更早版本，您必须使用FOR XML方法，而且它的效率较低，因为您必须多次查询表

WITH t AS (
SELECT 
a.title_id,
a.homeTeam AS team,
1 AS ordering
FROM TableA a
UNION ALL
SELECT 
b.title_id,
b.awayTeam,
2
FROM TableB b
UNION ALL
SELECT 
c.title_id,
c.homeTeam,
3
FROM TableC c
)
SELECT
tOuter.title_id,
STUFF(tInner.team.value('text()[1]', 'nvarchar(max)'), 1, LEN(', '), '') AS team
FROM (
SELECT DISTINCT t.title_id
FROM t
) tOuter
CROSS APPLY (
SELECT ', ' + ISNULL(t.team, 'N/A')
FROM t
WHERE t.title_id = tOuter.title_id
ORDER BY t.ordering
FOR XML PATH(''), TYPE
) tInner(team);

db<gt；小提琴

试试这个：

select
COALESCE(a.title_id,b.title_id,c.title_id),
CONCAT(
ISNULL(a.homeTeam,'N/A'),
ISNULL(b.awayTeam,'N/A'),
ISNULL(c.homeTeam,'N/A'),
) 'Teams',
from TableA a
full join TableB b on a.title_id = b.title_id
full join TableC c on c.title_id = a.title_id
where 
a.homeTeam is not null 
OR b.awayTeam is not null
OR c.homeTeam is not null
group by a.title_id

至于DISTINCT title_id，默认情况下它应该是唯一的，因为我假设它被用作每个表中的键。