mysql - 选择这个或那个但不能同时选择两者

Question

我有一个非常复杂的查询，让我的大脑都快崩溃了。

SELECT abunchOfStuff, r.xyz as netsell
FROM normalmtm n
JOIN mtmprogram m ON (
   m.mtmprogram = n.loanprogram
   AND m.investor = 'Xyzzy'
)
JOIN rates as r on (
    r.term >= '2012-01-01'
    AND r.investor = m.investor
    AND r.rate = n.rate
    AND r.program = m.basenormal
    AND r.clientcode = n.clientcode
)
LEFT OUTER JOIN mtmadjustments as ad ON (
    ad.loannumber = n.loannumber
    AND ad.investor = m.investor
)
WHERE n.loannumber = '12345678'
AND n.clientcode = 10
ORDER BY netsell DESC LIMIT 1

这是从一堆中选择最大的 netsell。现在，我无法完全理解的棘手部分是费率表可能与 n.clientcode 匹配，也可能不匹配。如果不匹配，我想使用0的clientcode，这本质上是一个通配符。

现在我正在执行特定于 clientcode 的查询，如果它没有返回任何内容，我将使用 clientcode = 0 再次执行此查询...似乎我可以以某种方式将这两件事包装在一起。

我查看了 EXISTS 或 NOT EXISTS 子查询，但我不知道如何在此示例中使用它，我什至不确定它是否合适。

感谢任何帮助!

Answer 1

SELECT abunchOfStuff, ISNULL(r.xyz, r2.xyz) as netsell
FROM normalmtm n
JOIN mtmprogram m ON (
   m.mtmprogram = n.loanprogram
   AND m.investor = 'Xyzzy'
)
LEFT OUTER JOIN rates as r on (
    r.term >= '2012-01-01'
    AND r.investor = m.investor
    AND r.rate = n.rate
    AND r.program = m.basenormal
    AND r.clientcode = n.clientcode
)
LEFT OUTER JOIN rates as r2 on (
    r2.term >= '2012-01-01'
    AND r2.investor = m.investor
    AND r2.rate = n.rate
    AND r2.program = m.basenormal
    AND r2.clientcode = 0
)
LEFT OUTER JOIN mtmadjustments as ad ON (
    ad.loannumber = n.loannumber
    AND ad.investor = m.investor
)
WHERE n.loannumber = '12345678'
AND n.clientcode = 10
ORDER BY netsell DESC LIMIT 1