sql - 如何在 SQL 中查询表的层次结构
问题描述
我有一张桌子,meeting
. 在这些meeting
s 中存在一个层次结构。其中一些是年度会议,大多数只是例会。
所有正则meetings
都将与至少一个连接表相关联,meeting_yearly_meeting
. Ameeting_yearly_meeting
有两列:meeting_id
和yearly_meeting_id
。
下面是这两个表的样子:
会议:
id SERIAL PRIMARY KEY,
title VARCHAR(255),
mappable BOOLEAN,
phone VARCHAR(255),
email VARCHAR(255),
city VARCHAR(255),
address VARCHAR(255),
zip VARCHAR(255),
latitude NUMERIC,
longitude NUMERIC,
description VARCHAR(255),
worship_time TIME,
state VARCHAR(255),
website VARCHAR(255),
lgbt_affirming BOOLEAN,
created TIMESTAMP default current_timestamp,
updated TIMESTAMP default current_timestamp
会议年度会议:
id SERIAL PRIMARY KEY,
meeting_id SMALLINT,
yearly_meeting_id SMALLINT,
created TIMESTAMP default current_timestamp,
updated TIMESTAMP default current_timestamp
因此,从我的/meetings
端点,我想返回所有会议的集合——包括定期会议和年度会议。我想返回会议及其所有列,以及一个附加列:yearly_meeting
.
对于meeting
具有一个或多个关联meeting_yearly_meeting
记录的记录,yearly_meeting
将是一个以逗号分隔的记录列表,title
该meeting
记录被指定为meeting
年度会议。对于那些没有任何相关meeting_yearly_meeting
记录的会议(因此它们本身就是年度会议),我想要yearly_meeting field to be
NULL`。
在追求这个目标的路上,我尝试了这样的事情:
SELECT t1.*, t2.meeting_yearly_meeting AS yearly_meeting
FROM (
SELECT * FROM meeting
FULL JOIN meeting_yearly_meeting ON meeting.id = meeting_yearly_meeting.yearly_meeting_id;
) as t1,
(
SELECT CASE WHEN (meeting_yearly_meeting.id IS NOT NULL)
THEN (SELECT title FROM meeting WHERE meeting.id = meeting_yearly_meeting.yearly_meeting_id)
ELSE NULL
END
FROM (
SELECT meeting_yearly_meeting.* FROM meeting
FULL JOIN meeting_yearly_meeting ON meeting.id = meeting_yearly_meeting.meeting_id
) as meeting_yearly_meeting;
) as t2;
但这会引发语法错误。
我很欣赏其他人可能有的任何见解。如果您需要任何其他上下文或说明,请告诉我!
更新:
样本meeting
数据:https ://gist.github.com/micahbales/4013399c3fd23a0caf108124dab827c8
样本meeting_yearly_meeting
数据:https ://gist.github.com/micahbales/fcbdeef282bd7bf1014606cee43bfb5e
预期返回值示例:https ://gist.github.com/micahbales/13d2aafdc5d43c4b948dc39c2df51569
解决方案
您可以尝试离开加入年度会议,然后使用string_agg()
以获取逗号分隔列表。
SELECT m1.id,
m1.title,
m1.mappable,
m1.phone,
m1.email,
m1.city,
m1.address,
m1.zip,
m1.latitude,
m1.longitude,
m1.description,
m1.worship_time,
m1.state,
m1.website,
m1.lgbt_affirming,
m1.created,
m1.updated,
string_agg(m2.title, ', ') yearly_meeting
FROM meeting m1
LEFT JOIN meeting_yearly_meeting mym1
ON mym1.meeting_id = m1.id
LEFT JOIN meeting m2
ON m2.id = mym1.yearly_meeting_id
GROUP BY m1.id,
m1.title,
m1.mappable,
m1.phone,
m1.email,
m1.city,
m1.address,
m1.zip,
m1.latitude,
m1.longitude,
m1.description,
m1.worship_time,
m1.state,
m1.website,
m1.lgbt_affirming,
m1.created,
m1.updated;
编辑:
更“紧凑”的解决方案可能是使用相关子查询。
SELECT m1.*,
(SELECT string_agg(m2.title, ', ')
FROM meeting_yearly_meeting mym1
LEFT JOIN meeting m2
ON m2.id = mym1.yearly_meeting_id
WHERE mym1.meeting_id = m1.id) yearly_meeting
FROM meeting m1;
但请注意,虽然它的代码更少,但不一定更快。
推荐阅读
- android - 如何在两个活动之间传递对象(Seriazale 不起作用)
- javascript - Mathjax 不适用于 firebase 上传的 html 文档
- c# - Xamarin File.WriteAllBytes 方法不写入数据
- java - java - 如果密码使用MD5加密,如何在服务器端验证密码规则?
- css - 避免宽度与空格重叠:pre
- r - 在 R 中默认禁用环境的恢复和保存
- python - 是否可以使用 sympy 来列出线图
- javascript - 设置 HTML Style 元素的“type”属性重置 css 规则
- qt - lineEdit 通过信号从不同的 Widget 调用 setText 后不表示文本
- python - 如何在表格中循环多个表单字段然后提交