从平面数据集中的父子表中检索数据 [英] To retrieve data from parent-child tables in a flat data set

问题描述

由于现有的数据库架构设计不好，我必须以一种特殊的方式编写一个选择语句，如这个线程.

Due to a bad existing DB schema design, I have to write a select statement in a special way as shown in this thread.

SELECT MAX(CASE WHEN Name = 'C' THEN RelativePath END) AS C,
   MAX(CASE WHEN Name = 'CC' THEN RelativePath END) AS CC,
   MAX(CASE WHEN Name = 'S' THEN RelativePath END) AS S,
   MAX(CASE WHEN Name = 'SC' THEN RelativePath END) AS SC,
   MAX(CASE WHEN Name = 'R' THEN RelativePath END) AS R,
   MAX(CASE WHEN Name = 'RC' THEN RelativePath END) AS RC
FROM myTable
WHERE ID = pId;

现在，我需要将其输出与其父表的 select 语句的输出连接起来.父子表的结构如下:

Now, I need to join its output with an output of a select statement of its parent table. The structure of the parent-child tables is the following:

Parent
-------------------
id    -> the primary key
third_party_id
createdBy
createdDate
...

Child
-------------------
id -> the primary key of its Parent table
Name 
...

对于给定的 id，我想要的 select 语句的输出结果类似于以下内容:

My desired output result of a select statement is something like the following for a given id:

third_party_id, createdBy, createdDate, C, CC, S, SC, R, RC

子表可能没有给定父表键的条目.我使用左连接使用 select 语句，但无法获得所需的结果.数据库是 mySQL.

The child table may not have an entry for a given parent table key. I play with select statements with left join, but can't get the desired result. The DB is mySQL.

如果单个select语句不起作用，如何为输出构造存储过程?

If a single select statement won't work, how to construct a stored procedure for the output?

推荐答案

SELECT
  Parent.third_party_id,
  Parent.createdBy,
  Parent.createdDate,
  Child.C,
  Child.CC,
  Child.S,
  Child.SC,
  Child.R,
  Child.RC
FROM
  Parent
LEFT OUTER JOIN
(
  SELECT 
     ID,
     MAX(CASE WHEN Name = 'C' THEN RelativePath END) AS C,
     MAX(CASE WHEN Name = 'CC' THEN RelativePath END) AS CC,
     MAX(CASE WHEN Name = 'S' THEN RelativePath END) AS S,
     MAX(CASE WHEN Name = 'SC' THEN RelativePath END) AS SC,
     MAX(CASE WHEN Name = 'R' THEN RelativePath END) AS R,
     MAX(CASE WHEN Name = 'RC' THEN RelativePath END) AS RC
  FROM myTable
  GROUP BY ID
) Child ON
  Child.ID = Parent.ID
WHERE
  Parent.ID = pId;

这篇关于从平面数据集中的父子表中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！