SQL相当于Oracle的“where()in”?

你需要 相关性 途径:

SELECT t.* 
FROM TABLE1 t
WHERE TIMESTAMP = (SELECT MAX(t1.TIMESTAMP) FROM TABLE1 t1 WHERE t1.ID = t.ID);

一个where-in条件可以通过内部连接轻松地重新计算

SELECT * FROM TABLE1 
INNER JOIN (
  SELECT MAX(TIMESTAMP) max_time,ID FROM TABLE1 GROUP BY ID
) t on t.max_time = TABLE1.TIMESTAMP and t.ID = TABLE1.ID

也可以使用分析查询:

SQL Fiddle

MS SQL Server 2017架构设置 :

CREATE TABLE Table1 ( ID int, ts DATETIME, rn INT );

INSERT INTO Table1 ( id, ts, rn )
SELECT 1, {ts '2018-01-01 00:00:00'}, 5 UNION ALL
SELECT 1, {ts '2018-01-01 01:00:00'}, 4 UNION ALL
SELECT 1, {ts '2018-01-01 02:00:00'}, 3 UNION ALL
SELECT 1, {ts '2018-01-01 03:00:00'}, 2 UNION ALL
SELECT 1, {ts '2018-01-01 04:00:00'}, 1 UNION ALL
SELECT 2, {ts '2018-01-01 00:00:00'}, 1 UNION ALL
SELECT 2, {ts '2018-01-01 01:00:00'}, 2 UNION ALL
SELECT 2, {ts '2018-01-01 02:00:00'}, 3 UNION ALL
SELECT 2, {ts '2018-01-01 03:00:00'}, 4 UNION ALL
SELECT 2, {ts '2018-01-01 04:00:00'}, 5;

查询1 :

SELECT *
FROM   (
  SELECT t.*,
         RANK() OVER ( PARTITION BY id ORDER BY ts DESC ) AS rnk
  FROM   TABLE1 t
) t
WHERE  rnk = 1

Results :

| ID |                   ts | rn | rnk |
|----|----------------------|----|-----|
|  1 | 2018-01-01T04:00:00Z |  1 |   1 |
|  2 | 2018-01-01T04:00:00Z |  5 |   1 |