SQL自连接返回特定行

Question

跳到底部以避免冗长的解释

好的，所以。

我正在为管理客户工作的公司内部网站工作。 工作由元素组成：例如，一个元素可能是“构建一个六页网站”，或者“设计一个标志”。

每个元素都包括一组角色小时，因此“构建一个六页网站”可能包括四个“开发人员”费率和两个“设计师”费率（好吧，可能有点长：）

显然，不同的客户享有不同的小时费率。 虽然系统已经考虑到了这一点，但它并没有给我们足够的灵活性。传统上，我们的客户经理在定价方面相当... 杂乱无章...：针对客户“鲍勃”，“构建一个六页网站”元素可能包括标准的四个开发人员小时，但对于客户“哈利”，则包括八个小时。

请耐心等待。 我很快会到达实际代码。

当然，元素存储在“元素”数据库表中-该表仅由ID和文本标签组成。

我正在进行的解决“我们需要客户特定元素”的问题的解决方案是向此表添加“客户端”字段。 然后，我们可以逐个添加任何可用元素的特定于客户的版本，以适应其口味。

当客户经理去添加元素到他们的工作时，他们应该只看到对所有人都可用的元素（即，它们具有NULL客户机字段）或特定于工作客户的元素。

到目前为止，选择是非常明显的。 但这还不够。 如果我为Harry专门添加第二个“构建六页网站”的元素，则为Harry添加元素的客户经理将同时看到标准版本和Harry的版本的元素。 这不好。 如果没有适用的特定于客户的版本，则只应查看标准版本。

好吧...所以：除了向元素表添加一个“客户端”字段之外，还要添加一个“父元素”字段。 我们然后可以做一些神奇的自我参照，将表加入自身，并仅获取相关角色。

我的漫长问题是：

哦看，一个实际的问题

id  label           client  parent_element
1   Standard Thing  NULL    NULL
2   Harrys Thing    1       1
3   Bobs Thing      2       1
4   Different Thing NULL    NULL

在给定的表结构下，我该如何编写一条SQL查询语句来接受一个“客户ID”参数并返回：

• 对于客户ID 1，返回第2行和第4行
• 对于客户ID 2，返回第3行和第4行
• 对于客户ID 42，返回第1行和第4行

额外加分项是，结果应包括父元素标签。例如，对于客户ID 1：

id  label           standardised_label      client  parent_element
2   Harrys Thing    Standard Thing          1       1
4   Different Thing Different Thing         NULL    NULL

- Wintermute

---

+1 针对“哦，看，一个实际的问题” - user359040

你“期待已久的问题”应该包括一个fiddle，除了你已经放上去的其他内容。 - hd1

如果您收到ID 42，您如何将其与1和4行相关联？很抱歉，我无法看出如何将每个结果与给定的客户ID相关联。 - Gonzalo.-

它们都应该返回第一行，对吧？因为它有一个“NULL”客户字段。 - MichaelJCox

@Mark Bannister：感谢您的格式化和澄清。 - Wintermute

Answer 1

SELECT  mm.*, md.label AS standardized_label
FROM    mytable md
LEFT JOIN
        mytable mc
ON      mc.parent_element = md.id
        AND mc.client = @client
JOIN    mytable mm
ON      mm.id = COALESCE(mc.id, md.id)
WHERE   md.client IS NULL

为了使其运行更快，需要在 (client, parent_element) 上创建索引。

请查看 SQLFiddle。

- Quassnoi