gpt4 book ai didi

sql - JOIN (SQL) 的 COALESCE 与 OR 条件

转载 作者:行者123 更新时间:2023-12-04 20:39:08 25 4
gpt4 key购买 nike

我有 Event

TABLE Event(
EventId [int] IDENTITY(1,1) NOT NULL,
EventSource1Id [int] NULL,
EventSource2Id [int] NULL
)

包含有关来自不同来源的事件的信息
其中事件源之一可以为空
TABLE EventSource1(
Id [int] IDENTITY(1,1) NOT NULL,
Name [nvarchar](50) NULL,
VenueId [int] NOT NULL
)


TABLE EventSource2(
Id [int] IDENTITY(1,1) NOT NULL,
Name [nvarchar](50) NULL,
VenueId [int] NOT NULL
)

TABLE Venue(
Id [int] IDENTITY(1,1) NOT NULL,
TimeZone [nvarchar](100) NOT NULL
)

我想创建 View ,但我不确定什么是最好的使用方法: coalesce vs OR condition for JOIN
第一个选项:
SELECT 
ev.[Id] AS 'Id',
ven.[Id] AS 'VenueId'
FROM Event ev
LEFT JOIN EventSource1 source1 ON source1.[Id] = ev.EventSource1Id
LEFT JOIN EventSource2 source1 ON source2.[Id] = ev.EventSource2Id
LEFT JOIN Venue AS ven ON ven.[Id] = source1.[VenueId] OR v.[Id] = source2.[VenueId]

第二种选择:
SELECT 
ev.[Id] AS 'Id',
ven.[Id] AS 'VenueId'
FROM Event ev
LEFT JOIN EventSource1 source1 ON source1.[Id] = ev.EventSource1Id
LEFT JOIN EventSource2 source1 ON source2.[Id] = ev.EventSource2Id
LEFT JOIN Venue AS ven ON ven.[Id] = COALESCE(source1.[Id], source2.[Id])

请问你能帮帮我吗?

最佳答案

COALESCE 通常会产生更好的查询计划。你应该用你的数据进行测试。

关于sql - JOIN (SQL) 的 COALESCE 与 OR 条件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38928055/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com