MySQL SELECT 最适合记录基于可选的 NULL 值

Question

请引用这个SQLFiddle:http://sqlfiddle.com/#!2/9db4f

“费率”表存储工作角色的每小时收费率，可以选择根据客户公司、组(“组”只是公司的一个部门)和客户联系人存储角色的不同费率。

费率也会随时间变化。

我想为给定的角色、公司、组和联系人组合选择一个最近的、最合适的费率。它应该按以下顺序尝试匹配:

1. client_contact、client_group、client_company 和角色
2. client_group、client_company 和角色
3. client_company 和角色
4. 只是角色

例如:我正在寻找匹配角色 ID 3、公司 ID 3 和客户 ID 4 的费率。

没有匹配所有这些的记录，因此它应该查找仅匹配角色 ID 3 和公司 ID 3 的记录。(其他字段 - client_contact 和 client_group - 必须为 NULL)。其中有两个:行 ID 的 2 和 3。它应该选择行 ID 3，因为它具有最近的“date_from”日期。

另一个示例:我正在寻找匹配角色 ID 3 和公司 ID 25 的费率。也没有其中之一，因此它应该寻找一个仅匹配角色 ID 3 的角色，并为所有其他值寻找 NULL。只有一个匹配行:数字 1。

当前 SQLFiddle 上的查询执行“获取最新”位，但我无法让它匹配可选列(如果存在)。

停止:(

编辑:哎呀，看起来 SQLFiddle 只保存模式，不保存查询。这是我得到的:

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

Answer 1

我会这样处理。

假设您正在寻找:

client_contact = 5
client_group= 3
client_company= 3
role = 3  

查询:

select *
from rate
where ifnull(client_contact, 5) = 5
    and ifnull(client_group, 3) = 3 
    and ifnull(client_company, 3) = 3 
    and ifnull(role, 3) = 3
order by case
    when client_contact = 5 and client_group = 3 and client_company = 3 and role = 3
      then 1
    when client_contact is null and client_group = 3 and client_company = 3 and role = 3
      then 2
    when client_contact is null and client_group is null and client_company = 3 and role = 3
      then 3
    when client_contact is null and client_group is null and client_company is null and role = 3
      then 4
end, date_from desc
limit 1

SQL Fiddle Example