tsql - SQL Server如何判断一个值是否改变

Question

我有一个包含三列的表：

Date, Symbol, Rankings

如何列出从昨天到今天排名变化的所有符号？

我可以很容易地得到今天的价值观：

SELECT TOP (1000) 
    [date], [symbol], [ranking]
FROM 
    [Rankings].[dbo].[Rankings]
WHERE
    CONVERT(VARCHAR(10), date, 102) = CONVERT(VARCHAR(10), GETDATE(), 102)
ORDER BY 
    symbol DESC

标签： tsqlsql-server-2012sql-date-functions

Answer 1

我在 SQL Server 中尝试过以下代码。有用。以下方法的优点是，它避免了在 WHERE 子句中使用函数，从而导致可搜索的查询。如果 Date 列具有正确的索引，则以下查询将提供良好的性能。

CREATE TABLE #rankings(symbol CHAR(10), [date] DATETIME, RANKING INT);

INSERT INTO #rankings VALUES ('MSFT','20190922', 1), ('MSFT','20190923',2), ('AMZN', '20190922', 3), ('AMZN','20190923',3);

DECLARE @todayStart DATETIME = CAST(GETDATE() AS DATE)
DECLARE @yesterDayStart DATETIME = DATEADD(DAY,-1,@todaystart)
SELECT @todayStart, @yesterDayStart


SELECT today.symbol, yesterday.ranking, today.ranking
FROM (SELECT * FROM #Rankings AS today WHERE today.date >= @todayStart) AS today
JOIN (SELECT * FROM #Rankings AS yesterday WHERE yesterday.date >= @yesterdayStart AND yesterday.date < @todayStart) AS yesterday
ON today.symbol = yesterday.symbol 
WHERE today.ranking <> yesterday.ranking