SQL自联接以返回特定行 [英] SQL self-join to return specific rows

问题描述

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

好的，

我正在公司的Intranet上管理客户的工作.工作由元素组成:一个示例元素可能是建立一个六页的网站"或设计徽标".

每个元素都包含一组角色时间，因此建立一个六页的网站"可能包括四个小时的开发人员"费率和两个小时的设计师"费率(好的，也许会更长一些:)/p>

很明显，不同的客户获得不同的小时费率.而且，尽管这已经在系统中解决了，但这并没有给我们带来足够的灵活性.传统上，我们的客户经理在价格上是临时的...临时的:建立一个六页的网站"元素可能包括标准的客户"Bob"四个小时的开发人员，但标准的客户"八小时的开发人员哈里".

与我同在.我将尽快获得实际代码.

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

我正在解决我们需要特定于客户端的元素"问题的解决方案是在此表中添加客户端"字段.然后，我们可以遍历并添加可用元素的任何特定于客户端的版本，并对其进行调整以使其具有品味.

当客户经理要向其工作中添加元素时，他们应该只看到以下元素:(a)任何人都可以使用-即，他们具有NULL客户字段，或(b)特定于工作客户的元素.

到目前为止，所以选择位置.

但这并不能解决问题.如果我添加第二个专门针对Harry的建立一个六页的网站"元素，那么将客户元素添加到Harry的工作中的客户经理将同时看到该元素的标准版本和Harry版本.不好如果没有适用的特定于客户的版本，他们应该只看到标准版本.

好吧... soooo:除了在元素表中添加客户端"字段外，还要添加父元素"字段.然后，我们可以做一些神奇的自引用操作，包括将表连接到自身，并仅获取相关角色.

因此，我期待已久的问题是:

哦，这是一个实际的问题

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

鉴于此表结构，我如何编写一个将接受客户端ID"参数并返回的SQL查询:

• 对于客户端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

解决方案

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 .

Skip to bottom to avoid long-winded explanation

Ok, so.

I'm working on a company intranet for managing client jobs. Jobs are comprised of Elements: an example element might be "Build a six-page website", or "Design a logo".

Each element consists of a collection of role-hours, so "Build a six-page website" might include four hours of "Developer" rate and two hours of "Designer" rate (ok, maybe a little longer :)

Obviously, different clients get different hourly rates. And, although that's already accounted for in the system, it's not giving us enough flexibilty. Traditionally, our account managers have been rather... ad hoc... with their pricing: the "Build a six-page website" element might include the standard four hours of developer for client "Bob", but eight hours for client "Harry".

Bear with me. I will get to actual code soon.

Elements are, of course, stored in the "Elements" database table - which is composed of little more than an ID and a text label.

My work-in-progress solution to the "we need client-specific elements" problem is to add a "client" field to this table. We can then go through and add any client-specific versions of the available elements, tweaking them to taste.

When the account managers go to add elements to their jobs, they should only see elements that are either (a) available to anyone - that is, they have a NULL client field, or (b) specific to the job client.

So far, so SELECT WHERE.

But that isn't going to cut it. If I add a second "Build a six-page website" element specifically for Harry, then an account manager adding elements to a job for Harry will see both the standard version, and Harry's version of the element. This is no good. They should only see the standard version if there's not an applicable client-specific version.

Ok... soooo: as well as adding a "client" field to the elements table, add a "parent element" field. We can then do something magically self-referential involving joining the table to itself, and fetch only the relevant roles.

My long-awaited question is thus:

Oh look, an actual question

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

Given this table structure, how can I write a single SQL query that will accept a "client ID" parameter and return:

• For client ID 1, rows 2 and 4
• For client ID 2, rows 3 and 4
• For client ID 42, rows 1 and 4

For extra bonus points, the results should include the parent element label. So for client ID 1, for example:

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

解决方案

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

Create an index on (client, parent_element) for this to work fast.

See SQLFiddle.

这篇关于SQL自联接以返回特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！