gpt4 book ai didi

sql - 如何在SQL中实现这种双重联接?

转载 作者:行者123 更新时间:2023-12-03 18:26:48 25 4
gpt4 key购买 nike

我有此查询,它将存储在缓存表中的事件的名称联接起来。

SELECT OccurrenceCache.occurrence_date, CalendarItem.summary FROM OccurrenceCache
INNER JOIN CalendarItem ON CalendarItem.ROWID = OccurrenceCache.event_id
WHERE OccurrenceCache.occurrence_date >= (strftime('%s', 'now', 'localtime', 'start of day') - strftime('%s', '2001-01-01', 'start of day')) AND OccurrenceCache.occurrence_end_date <= (strftime('%s', 'now', 'localtime', 'start of day') - strftime('%s', '2001-01-01', 'start of day') + 24 * 60 * 60);


现在,我也想包括事件的位置数据,这些数据存储在名为Location的表中。位置条目由CalendarItem.location_id引用(0表示未指定位置)。我尝试了另一个JOIN语句,但它不起作用:

SELECT OccurrenceCache.occurrence_date, CalendarItem.summary, Location.title FROM OccurrenceCache
INNER JOIN CalendarItem ON CalendarItem.ROWID = OccurrenceCache.event_id
INNER JOIN Location ON Location.ROWID = CalendarItem.location_id
WHERE OccurrenceCache.occurrence_date >= (strftime('%s', 'now', 'localtime', 'start of day') - strftime('%s', '2001-01-01', 'start of day')) AND OccurrenceCache.occurrence_end_date <= (strftime('%s', 'now', 'localtime', 'start of day') - strftime('%s', '2001-01-01', 'start of day') + 24 * 60 * 60);


它返回0个结果。

最佳答案

如果Location表中的所有条目都不匹配calenderitem表,则使用LEFT JOIN将是解决方案。

SELECT OccurrenceCache.occurrence_date, CalendarItem.summary, Location.title FROM OccurrenceCache
INNER JOIN CalendarItem ON CalendarItem.ROWID = OccurrenceCache.event_id
LEFT JOIN Location ON Location.ROWID = CalendarItem.location_id
WHERE OccurrenceCache.occurrence_date >= (strftime('%s', 'now', 'localtime', 'start of day') - strftime('%s', '2001-01-01', 'start of day')) AND OccurrenceCache.occurrence_end_date <= (strftime('%s', 'now', 'localtime', 'start of day') - strftime('%s', '2001-01-01', 'start of day') + 24 * 60 * 60);


Using Outer Joins in SQL

关于sql - 如何在SQL中实现这种双重联接?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14909512/

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