sql - 根据其他表中的两个日期选择值？

Question

我有一个 SQL 问题。数据库是 Firebird，但这无关紧要，因为我想尽可能使用 ANSI 标准。

我必须根据用户资格和用户被分配该资格的日期来选择费用率(我希望这听起来是正确的)。我有这样的表格:

Usertable   Variables      Qualification    Rapportcostrate    Timeaccount
=========   =========      =============    ===============    ===========
UserId      VarId          QuaId            RcrId              TiaID
UserName    VarUserId      QuaName          RcrName            TiaValue
            VarQuaId       QuaRcrId         RcrRate            TiaDate
            VarValidFrom                                       TiaUserId

我现在需要得到的是:

UserName, RcrRate, TiaValue, TiaDate

我的问题是资格随时可能改变，之前输入的资格可能仍然有效。这应该计算(或选择)

RcrRate * TiaValue 

基于 VarValidFrom 和 TiaDate 中的值。我不能让他们在一起! :-(

我表达清楚了吗？但愿如此!欢迎随时提问!

== 20 小时后编辑 ==

我现在正在使用这个 sql 命令:

SELECT RcrRate, VarValidFrom, TiaValue FROM USERTABLE
    JOIN VARIABLES ON UserId = VarUserId
    JOIN QUALIFICATIONS ON VarQuaId = QuaId
    JOIN RAPPORTCOSTRATE ON QuaRcrId = RcrId
    JOIN TIMEACCOUNT ON UserId = TiaUserId
WHERE UserId = XYZ

现在假设表 qualifications 填充了几个集合

 Qualifications          Rapportcostrate
 ===============         ===============
 1    Dummy    1         1   Value01   20
 2    Testing  2         2   Value02   30
 3    Another  3         3   Value03   40
 4    FooBar   4         4   Value04   50

如果用户 xyz 在 Variables 中有三个条目，例如

 Variables
 =========
 1    1    1    2008-03-01
 2    1    2    2009-04-01
 3    1    3    2010-05-10
 4    1    4    2013-12-12

预期结果应返回适合 VarValidFrom 日期到 TiaDate 的 RcrRate。我希望每个 TiaDate 只收到一行。

...让自己清楚是一场相当大的斗争...;-)

Answer 1

对此有多种解决方案，在下面的解决方案中，我使用 CTE(公用表表达式)将工作拆分为逻辑单元。 cteVarTimes 选择Variables 和Timeaccount 的所有有效组合，然后cteMostRecent 为用户选择最新的。在主要选择中，我将其与来自 Qualifications 和 Rapportcostrate 的信息相结合。

如果您需要能够为所有用户返回行，则此解决方案不起作用。在这种情况下，您需要将 cteMostRecent 替换为一个查询，该查询使用每个用户的最大 VarValidFrom 和 TiaDate 来选择行。

WITH cteVarTimes AS (    
    SELECT v.VarUserId as UserId, v.VarQuaId, tia.TiaValue, tia.TiaDate, v.VarValidFrom
    FROM Variables v
    INNER JOIN Timeaccount tia
        ON tia.TiaUserId = v.VarUserId
    WHERE tia.TiaDate >= v.VarValidFrom
),
cteMostRecent AS (
    SELECT UserId, VarQuaId, TiaValue, VarValidFrom
    FROM cteVarTimes
    WHERE UserId = 1 -- Parameter in real use
    ORDER BY VarValidFrom DESC, TiaDate DESC
    ROWS 1
)
SELECT RcrRate, TiaValue, VarValidFrom
FROM cteMostRecent a
INNER JOIN Qualifications q
    ON q.QuaId = a.VarQuaId
INNER JOIN Rapportcostrate rcr
    ON rcr.RcrId = q.QuaRcrId