不是所需日期但第一个可用的SQL值

我个人会为这种类型的事情转移到一个用户定义的函数,而不是一个连接。这样,您就可以完全控制评估过程。根据你的笔记,你可以按照如下的方式来做。

SELECT TOP 1 kurs_sredni
FROM YourTable
WHERE (Your Comparison here)
ORDER BY Date 

这样,您就可以在“日期”上进行>=比较,如果它不存在,您将得到下一个最新的日期值。

我不能尝试使用MS SQL,但类似的方法应该对您有效。

它应该返回日期差异最小的值(如果可能,返回相同的日期)。

SELECT * FROM (
    SELECT  t1.[TransakcjeGotowkoweKwota],  
            t1.TypyWaluty,
            t2.[kurs_sredni],
            ROW_NUMBER() OVER( PARTITION BY t1.[TransakcjeGotowkoweData] ORDER BY ABS(cast(t1.[TransakcjeGotowkoweData] - t2.[data publikacji] AS FLOAT)) ) rank
    FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP] t2
    ON t1.TypyWaluty = t2.[kod waluty]
    WHERE   [TypyWaluty] = 'EUR'
) x
WHERE rank = 1

一 helper table of dates 这是有用的;加入到Kursywalutnbp,你可以很容易地计算出每个日期的正确汇率。我将此作为一个视图进行展示,以简化最终查询:

CREATE VIEW CurrencyNearRates (kod_waluty, data, kurs_sredni)
AS 
SELECT currencydates.kod_waluty, currencydates.data, ratenow.kurs_sredni FROM (SELECT currencies.kod_waluty, Helper_Dates.data FROM currencies CROSS JOIN Helper_Dates) currencydates
LEFT OUTER JOIN KursyWalutNBP ratenow
ON 
currencydates.kod_waluty = ratenow.kod_waluty
AND currencydates.data <= ratenow.data_publikacji
AND ratenow.data_publikacji = 
(
  SELECT MIN(futurerates.data_publikacji)
  FROM KursyWalutNBP futurerates
  WHERE ratenow.kod_waluty = futurerates.kod_waluty
  AND currencydates.data <= futurerates.data_publikacji
) 

这给了你这样的数据:

SELECT * FROM CurrencyNearRates ORDER BY kod_waluty, data;
|kod_waluty     |data        |kurs_sredni|
|----------------------------------------|
|EUR            |2009-01-04  |4.1137     |
|EUR            |2009-01-05  |4.1137     |
|EUR            |2009-01-06  |4.4157     |
|EUR            |2009-01-07  |4.4157     |
 ----------------------------------------

然后,您只需在Transakcjegotowkowe中的交易和货币汇率之间进行简单的连接:

SELECT
 t1.[TransakcjeGotowkoweKwota],  
 t1.[TypyWaluty],  
 t1.[TransakcjeGotowkoweData],  
 CurrencyNearRates.[kurs_sredni]
FROM
dbo.[TransakcjeGotowkowe] t1
LEFT OUTER JOIN CurrencyNearRates
 ON t1.[TypyWaluty] = CurrencyNearRates.[kod_waluty]
 AND t1.[TransakcjeGotowkoweData] = CurrencyNearRates.[data]
WHERE t1.[TypyWaluty] = 'EUR'  
ORDER BY t1.[TransakcjeGotowkoweData]

这给了你这样的输出:

|TransakcjeGotowkoweKwota   |TypyWaluty   |TransakcjeGotowkoweData   |kurs_sredni   |
|-----------------------------------------------------------------------------------|
|-18.36                     |EUR          |2009-07-01                |4.4157        |
|-14153.04                  |EUR          |2009-01-05                |4.1137        |
|4.61                       |EUR          |2007-09-30                |4.5678        |
|55.5                       |EUR          |2007-09-30                |4.5678        |
 -----------------------------------------------------------------------------------

我在理解您的查询时遇到了一些困难,但这是对我认为您要解决的问题的一般解决方案。如果每个父级可能有许多子级,那么您可能希望按开始日期限制(例如,在左侧外部联接和子查询中包括条件,这样它就只限制在@my_date的3天内的子级(例如)。至少在这个日期的索引可能会得到一些使用,而下面的代码根本无法使用它。

SELECT
    P.parent_id,
    C.stuff
FROM
    Parent P
LEFT OUTER JOIN Child C ON
    C.parent_id = P.parent_id
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            Child C2
        WHERE
            C2.parent_id = P.parent_id AND
            ABS(DATEDIFF(ss, C2.my_date, @my_date)) < ABS(DATEDIFF(ss, C.my_date, @my_date))
    )

像这样的事情应该可以做到:

SELECT  t1.[TransakcjeGotowkoweKwota],  
        t1.TypyWaluty,  
        t1.[TransakcjeGotowkoweData],  
        t2.[kurs_sredni]  
FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1
LEFT JOIN (
    select ta.TypyWaluty, ta.[TransakcjeGotowkoweData], min(ABS(cast(ta.[TransakcjeGotowkoweData] - tb.[data publikacji] as float))) as ClosestDate
    FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] ta
    inner join [BazaZarzadzanie].[dbo].[KursyWalutNBP] tb ON ta.TypyWaluty = tb.[kod waluty] 
    group by ta.TypyWaluty, ta.[TransakcjeGotowkoweData]
) t2c ON t1.TypyWaluty = t2c.TypyWaluty
    AND t1.[TransakcjeGotowkoweData] = t2c.[TransakcjeGotowkoweData]
LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP] t2 ON t2c.TypyWaluty = t1.TypyWaluty
    AND t1.[TransakcjeGotowkoweData] = t2.[TransakcjeGotowkoweData]
    AND t2c.ClosestDate = ABS(cast(t1.[TransakcjeGotowkoweData] - t2.[data publikacji] as float))
WHERE   t1.[TypyWaluty] = 'EUR'

最好的办法是米切尔的。我创造了

CREATE FUNCTION KursWaluty
(
  @typWaluty nvarchar(15),
  @dataWaluty DATETIME
)
RETURNS varchar(30)
AS BEGIN
RETURN ( SELECT TOP 1
                kurs_sredni
         FROM   [BazaZarzadzanie].[dbo].[KursyWalutNBP]
         WHERE  [kod waluty] = @typWaluty
                AND [data publikacji] >= @dataWaluty
         ORDER BY [data publikacji]
       )

end

并使用此查询获取:

SELECT  t1.[TransakcjeGotowkoweKwota],
    TypyWaluty,
    [TransakcjeGotowkoweData],
    CASE WHEN [kurs_sredni] IS NULL
         THEN BazaZarzadzanie.dbo.KursWaluty(TypyWaluty, [TransakcjeGotowkoweData])
         ELSE [kurs_sredni]
    END AS 'Currency'

FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfeleKonta] t2
    ON t1.[KlienciPortfeleKontaID] = t2.[KlienciPortfeleKontaID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP]
    ON TypyWaluty = [kod waluty]
       AND [data publikacji] = [TransakcjeGotowkoweData]
 WHERE   [TypyWaluty] = 'EUR' -- AND [kurs_sredni] IS NULL

这很有效,而且看起来很快(2秒)。我已经使用并且[kurs_sredni]为空来验证空值现在是否得到了正确的值。