我认为您的第一步将是以可用的格式获取膳食和团队表。使用这些怪异的串联列永远都不是容易或快速的。
做一些简单的像这样分割的ID在不同的行:
select trim(column_value) partner_id,
team
from teams,
xmltable(('"' || replace(teams.concat_identifier_partner, ']',']","') || '"'))
给出这样的输出:
+------------+------+
| PARTNER_ID | TEAM |
+------------+------+
| [012345] | Blue |
| [012346] | Blue |
| [012347] | Red |
+------------+------+
然后,您可以将它们用作查询中的内联视图,或为每个视图创建一个新的临时表。临时表将允许您在新的ID列上为它们建立索引。
数据采用简单格式后,联接变得简单。
select people.identifier,
people.name,
formatted_teams.team,
formatted_meals.meal
from people
join (select trim(column_value) partner_id,
team
from teams,
xmltable(('"' || replace(teams.concat_identifier_partner, ']',']","') || '"')) ) formatted_teams
on people.identifier = formatted_teams.partner_id
left join (select trim(column_value) plate_id,
meal
from meals,
xmltable(('"' || replace(meals.concat_identifier_plate, ']',']","') || '"')) ) formatted_meals
on people.identifier = formatted_meals.plate_id
order by people.identifier
输出:
+------------+--------+------+--------+
| IDENTIFIER | NAME | TEAM | MEAL |
+------------+--------+------+--------+
| [012345] | George | Blue | Salmon |
| [012346] | Jenny | Blue | |
| [012347] | Alex | Red | Pike |
| [012347] | Alex | Red | Cod |
+------------+--------+------+--------+
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句