如何基于tblAltID获取tblID中指定ID及关联AltID的业务数据？

我有两张数据表，结构如下：

tblID (ID, Quantity, Project, Region, Date)
tblAltID(ID, AltID)

示例数据 #

tblID #

tblAltID #

需求目标 #

指定某个ID，获取该ID及其所有关联AltID在tblID中的对应数据。比如指定ID='123'时，要获取123、456、789这三个ID的Quantity、Project、Region、Date数据，期望结果如下：

期望输出 #

当前编写的SQL（存在问题） #

Select a.ID, b.AltID, a.Quantity, a.Project, a.Region, a.Date
From tblID as a
Inner Join (Select ID, AltID From tblAltID CROSS JOIN UNNEST(AltID)) as b on a.ID = b.ID
Where ID = '123'
and Date = (Select max(Date) from tblID
Order By Quantity Desc

补充说明：所有关联的AltID均存在于tblID中，例如查询ID=456时，可获取其对应数据，此时123和789将作为其关联ID。

由于tblAltID中的AltID是字符串格式的数组（如[456,789]），首先需要将其转换为可展开的数组类型，再结合主ID和展开后的AltID去关联tblID。

针对BigQuery的实现 #

WITH expanded_alts AS (
  SELECT
    t.ID AS main_id,
    TRIM(alt) AS alt_id
  FROM tblAltID t
  CROSS JOIN UNNEST(SPLIT(REGEXP_REPLACE(t.AltID, r'^\[|\]$', ''), ',')) alt
  WHERE t.ID = '123' -- 指定目标ID
  UNION ALL
  -- 加入主ID本身，对应AltID为null的行
  SELECT
    '123' AS main_id,
    NULL AS alt_id
)
SELECT
  ea.main_id AS ID,
  ea.alt_id AS AltID,
  ti.Quantity,
  ti.Project,
  ti.Region,
  ti.Date
FROM expanded_alts ea
LEFT JOIN tblID ti ON COALESCE(ea.alt_id, ea.main_id) = ti.ID
ORDER BY CASE WHEN ea.alt_id IS NULL THEN 0 ELSE 1 END, ea.alt_id;

针对MySQL 8.0+的实现 #

WITH expanded_alts AS (
  SELECT
    t.ID AS main_id,
    JSON_UNQUOTE(json_val) AS alt_id
  FROM tblAltID t
  CROSS JOIN JSON_TABLE(
    TRIM(BOTH '[]' FROM t.AltID),
    '$[*]' COLUMNS(json_val VARCHAR(20) PATH '$')
  ) jt
  WHERE t.ID = '123' -- 指定目标ID
  UNION ALL
  -- 加入主ID本身
  SELECT
    '123' AS main_id,
    NULL AS alt_id
)
SELECT
  ea.main_id AS ID,
  ea.alt_id AS AltID,
  ti.Quantity,
  ti.Project,
  ti.Region,
  ti.Date
FROM expanded_alts ea
LEFT JOIN tblID ti ON COALESCE(ea.alt_id, ea.main_id) = ti.ID
ORDER BY CASE WHEN ea.alt_id IS NULL THEN 0 ELSE 1 END, ea.alt_id;

内容的提问来源于stack exchange，提问作者MakanCoding