GROUP CONCAT和LEFT OUTER JOIN进行拆分

Question

我正在使用SQL工作台。这是我有的表格的一个例子：

SITES
id
BUILDINGS
id
site_id
LEVELS
id
building_id

我还没有创建这个数据库，也无法修改它。每个建筑都对应一个网站，所以它包含一个指向该网站的KEY。与标高和建筑物相同。

我正在尝试得到这个结果

site_id   |   buildings_id   |   levels_id
1         |   15, 16         |   (213, 214), (313, 314)
2         |   21, 22, 23     |   (350), (400, 401, 402), (501)
3         |   31, 32, 33, 34 |   (5001, 5002), (7001), (8009), (8400)

前两列很容易，但我对第三列有问题。括号是说明性的，我不一定需要。但我需要一种通过building_id将字符串拆分为级别的方法。

每个(…(对应于一栋建筑中的标高。

这是我现在处理的查询，它返回第一列和第二列。我尝试向查询添加另一个LEFT OUTER JOIN，但没有成功。我尝试了很多事情，但我尝试的大多数事情都会返回错误或奇怪的输出。

SELECT 
sites.id AS site_id,
GROUP_CONCAT(DISTINCT buildings.id
SEPARATOR '; ') AS buildings_id
FROM
sites
LEFT OUTER JOIN
users_has_sites ON users_has_sites.sites_id = sites.id
LEFT OUTER JOIN
users ON users.id = users_has_sites.users_id
LEFT OUTER JOIN
buildings ON buildings.sites_id = sites.id
WHERE
users.id = 42
GROUP BY site_id;

感谢您的帮助。感谢

Answer 1

您需要两个级别的聚合：一个由building_id聚合，另一个由site_id聚合。

with b_l as (
select
b.id as building_id
, b.site_id
, concat('('
, group_concat(
l.id
order by l.id
separator ','
)
, ')'
) as building_levels
from buildings as b
left join levels as l
on b.id = l.building_id
group by b.id, b.site_id
)
, s_b as (
select
s.id
, group_concat(
b_l.building_id
order by b_l.building_id
separator ','
) as buildings
, group_concat(
b_l.building_levels
order by b_l.building_id
separator ','
) as levels
from sites as s
left join b_l
on s.id = b_l.site_id
group by s.id
)
select *
from s_b

id|buildings|levels-：|：--------|：----------------------1|1,2,3|(1,2(，(3,4,5(2|4,5,6|(7,8(，(9(，(10,11(3|7,8,9|(12,13,14(，(15,16(，(17(4|10|null

或与横向连接相同：

select
s.id
, group_concat(
b_l.building_id
order by b_l.building_id
separator ','
) as buildings
, group_concat(
b_l.building_levels
order by b_l.building_id
separator ','
) as levels
from sites as s
left join lateral (
select
b.site_id
, b.id as building_id
, concat('('
, group_concat(
l.id
order by l.id
separator ','
)
, ')'
) as building_levels
from buildings as b
left join levels as l
on b.id = l.building_id
group by b.id, b.site_id
) b_l
on s.id = b_l.site_id
group by s.id

db<gt；小提琴这里

Answer 2

根据您的需求和表结构推断你可以试试这个sql

选择site.id、GROUP_CONNCT(c.building_id(building_id、GROUP-CONNCT(c.level_id(levels_id从现场(选择building.site_id，building.id building_id，concat('('，GROUP_CONCT(level.id(，'('(level_id从建筑，levelWHERE building.id=level.building_id GROUP BY building.id)c，其中site.id=c.site_id按站点分组.id；

Answer 3

您可以使用两个级别的聚合，从最低级别开始：

SELECT site_id,
GROUP_CONCAT(building_id ORDER BY building_id SEPARATOR ', ') as building_ids,
GROUP_CONCAT('(', levels, ')' ORDER BY building_id SEPARATOR ', ') as levels
FROM (SELECT s.id AS site_id, b.id as building_id,
GROUP_CONCAT(l.id ORDER BY l.id SEPARATOR ', ') as levels
FROM sites s LEFT JOIN
users_has_sites uhs
ON uhs.sites_id = s.id LEFT JOIN
buildings b
ON b.sites_id = s.id LEFT JOIN
levels l
ON l.building_id = b.id
WHERE uhs.users_id = 42
GROUP BY site_id, b.id
) sb
GROUP BY site_id;

请注意，不需要users表，因为id在junction表中。此外，可能不需要sites，但您正在将其用于LEFT JOIN(尽管WHERE子句将其转换为INNER JOIN(。不过我把它忘了。