如何将这两个SQL查询合并为一个？

SQL Server 2005. Table schema is MarketdataID, Datatype, Date, Source, Coordinate, Value. PK is everything except Value. Data may be available from multiple sources and may not be available for the given date; I want to get the most recent date before the given date, and only one source per date.

SQL Server 2005.表模式是MarketdataID，数据类型，日期，来源，坐标，值。 PK是除Value之外的一切。数据可能来自多个来源，可能在指定日期不可用;我希望在给定日期之前获得最近的日期，并且每个日期只有一个来源。

SELECT top 1 [Source], [Date] FROM Market
WHERE MarketDataID = ?
AND DataType = ?
AND [Date] <= ?
order by [date] desc, [source]

then use the returned date and source in this query:

然后在此查询中使用返回的日期和来源：

SELECT [Coordinate], [Value] FROM Market
WHERE MarketDataID = ?
AND DataType = ?
AND [Date] = ?
AND [Source] = ?
ORDER BY [coordinate]

1 个解决方案

#1

SELECT [Coordinate], [Value]
  FROM Market AS m0
  JOIN (SELECT TOP 1 m2.[Source], m2.[Date], m2.MarketDataID, m2.DataType
          FROM Market AS m2
         WHERE m2.MarketDataID = ?
           AND m2.DataType = ?
           AND m2.[Date] <= ?
           ORDER BY m2.[Date] DESC, m2.[Source]
       ) AS m1
    ON m0.[Source]     = m1.[Source]
   AND m0.[Date]       = m1.[Date]
   AND m0.MarketDataID = m1.MarketDataID
   AND m0.DataType     = m1.DataType
 ORDER BY [coordinate]