☰

如何让 SQL Server 返回在单独表中定义的多个时间范围（或任何值）中的任意一个范围内的记录？

tag-icon • tag-icon sql-server sql
如何让 SQL Server 返回在单独表中定义的多个时间范围（或任何值）中的任意一个范围内的记录？

下面的代码澄清了标题中的问题。在 WHERE 中可以放什么才能使其工作？

CREATE TABLE #PickedTimes
(StartTime smalldatetime,
 EndTime smalldatetime)

INSERT INTO #PickedTimes
VALUES  ('2019-01-25 16:05', '2019-01-25 17:05'),
        ('2019-01-25 19:05', '2019-01-25 20:05')
--Each row is a time range. There would be more in the real situation, and it is desirable to define them this way.

SELECT * FROM #PickedTimes

-- No questions up to here.

SELECT TransactionID, Timestamp
FROM Transactions
WHERE Timestamp -- SOMETHING WITH #PickedTimes. How to make it pick any of the time ranges in #PickedTimes?

答案1

也许你需要这个：

SELECT TransactionID, Timestamp
FROM Transactions INNER JOIN #PickedTimes
WHERE Transactions.Timestamp >= #PickedTimes.StartTime 
    AND Transactions.Timestamp <= #PickedTimes.EndTime

相关内容