How can I rewrite Join on an optional null value into a long-tail question?

本文共计962个文字，预计阅读时间需要4分钟。

我在以下查询中遇到了问题：SELECT job.name, job_element.label, job_element_role_h

我遇到了以下问题：SELECT job.name, job_element.label, job_element_role_h

Imhavingtroublewiththefollowingquery:我遇到以下问题时遇到问题:SELECTjob.name,job_element.la

I'm having trouble with the following query:

我遇到以下问题时遇到问题:

SELECT job.name, job_element.label, job_element_role_hours.role, job_element_role_hours.hours_budgeted, latest_rate.hourly_rate, ( job_element_role_hours.hours_budgeted * latest_rate.hourly_rate ) AS line_costFROM job_element INNER JOIN job ON job_element.job = job.id INNER JOIN job_element_role_hours ON job_element_role_hours.element = job_element.id LEFT JOIN( SELECT rate.* FROM rate LEFT JOIN rate AS newest ON ( rate.role = newest.role AND COALESCE(rate.client_company, 1) = COALESCE(newest.client_company, 1) AND COALESCE(rate.client_group, 1) = COALESCE(newest.client_group, 1) AND COALESCE(rate.client_contact, 1) = COALESCE(newest.client_contact, 1) AND newest.date_from > rate.date_from ) WHERE newest.id IS NULL ) AS latest_rate ON ( latest_rate.role = job_element_role_hours.role AND ( COALESCE(latest_rate.client_company, 1) = COALESCE(job.client_company, 1) OR latest_rate.client_company IS NULL ) AND ( COALESCE(latest_rate.client_group, 1) = COALESCE(job.client_group, 1) OR latest_rate.client_group IS NULL ) AND ( COALESCE(latest_rate.client_contact, 1) = COALESCE(job.client_contact, 1) OR latest_rate.client_contact IS NULL ) )WHERE job.id = 4

So... this query fetches a list of Elements for a Job. Each Element is broken down by "Role Hours" - an Element called "Build an HTML email", for example, might have two hours of Designer, and two hours of Developer - and then the calculates a cost based on the Hourly Rate for each Role.

所以...此查询获取作业的元素列表。每个元素按“角色小时”细分 - 例如,一个名为“构建HTML电子邮件”的元素,可能有两个小时的Designer,两个小时的开发人员 - 然后根据每个小时的每小时费率计算一个成本角色。

The issue is where I join the latest_rate subquery back to the main query. Here's my rates table:

问题是我将latest_rate子查询加入主查询。这是我的费率表:

'CREATE TABLE `rate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `client_company` int(11) DEFAULT NULL, `client_group` int(11) DEFAULT NULL, `client_contact` int(11) DEFAULT NULL, `role` int(11) DEFAULT NULL, `date_from` datetime DEFAULT NULL, `hourly_rate` decimal(18,2) DEFAULT NULL, `last_edited_by` int(11) DEFAULT NULL, PRIMARY KEY (`id`))

There might be several rates for a role: a "global" rate where the role is set but the client_company, client_group and client_contact fields are NULL; a company-specific rate where the role and client_company are set but everything else is NULL, and a client-specific rate where the role, client_company and client_contact is set but the client_group is NULL.

角色可能有多种速率:设置角色的“全局”速率,但client_company,client_group和client_contact字段为NULL;一个特定于公司的速率,其中设置了角色和client_company但其他所有内容都为NULL,以及特定于客户端的速率,其中设置了角色client_company和client_contact但client_group为NULL。

I want to get the one rate for the role that "most closely" matches the client_company, client_group and client_contact set for the Job. So if there's a Rate record with the role and client_company set (and the client_company matches the one for the Job), I want that one. Otherwise, I'll fall back to the one with NULL client_company.

我想获得“最接近”匹配为作业设置的client_company,client_group和client_contact的角色的一个速率。因此,如果设置了角色和client_company的Rate记录(并且client_company与Job的匹配),我想要那个。否则,我将回退到具有NULL client_company的那个。

My attempt at joining is clearly wrong:

我加入的尝试显然是错误的:

AND ( COALESCE(latest_rate.client_company, 1) = COALESCE(job.client_company, 1) OR latest_rate.client_company IS NULL)

It'll return all the records that either match the client_company, or have a NULL client_company.

它将返回与client_company匹配的所有记录,或者具有NULL client_company。

How can I get it to just get the one with the most matching fields?

如何才能获得具有最匹配字段的那个?

Thanks :)

1 个解决方案

#1

2

What you could try is have one query that basically gets all matching rates against one parameter that you absolutely want to match - seems to be role in your case.

您可以尝试的是有一个查询基本上可以获得您想要匹配的一个参数的所有匹配率 - 在您的情况下似乎是角色。

Then you can do soemthing like:

然后你可以这样做:

MAX( CASE WHEN THEN END, CASE WHEN THEN END, ... )

This would give you the maximum rate possible, i.e, the rate applicable if you had the most matching fields.

这将为您提供可能的最大速率,即,如果您具有最匹配的字段,则适用的速率。