根据第一个表的输入从第二个表获取字段的SQL查询

Question

我有两个表，table1和table2

表1 -

时间戳123456

获取"最新的";我总是使用窗口函数。

为了保持一切干净，我使用了两个cte来定义两个数据集，但如果需要，您可以将所有内容放在一个查询中。

WITH latestDataT1 AS (
SELECT 
sn
,timestamp
FROM (
SELECT
sn
,timestamp
,ROW_NUMBER() OVER(ORDER BY timestamp DESC) as RowNo
FROM table1
)
WHERE
RowNo = 1
)
WITH latestDataT2 AS (
SELECT
sn
,timestamp
,code
FROM (
SELECT 
sn
,timestamp
,code
,ROW_NUMBER() OVER(PARTITION sn BY ORDER BY timestamp DESC) as RowNo
FROM table2
)
WHERE
RowNo = 1
)
SELECT
*
FROM latestDataT1 AS t1
LEFT JOIN latestDataT2 AS t2
ON t1.sn = t2.sn

你可以试试这些脚本吗:

1)选择代码从表二where (sn, timestamp) in (select sn, max(timestamp) from table1 GROUP BY sn);

2)

select A.code, B.maxtime 
from table2 A,  
(select sn, max(timestamp) as maxtime 
from table1 GROUP BY sn) B
where A.sn        = B.sn
and A.timestamp = B.maxtime;

谢谢

试试这个:

select DISTINCT code from table2 
where sn = (select sn from table1 WHERE timestamp = (SELECT MAX(timestamp) FROM Table1));

可以使用以下代码:

SELECT code
from table2 t
where sn = (SELECT sn
FROM table1 t1
INNER JOIN (SELECT MAX(timestamp) AS max_ts
FROM table1) d
ON t1.timestamp = d.max_ts
)
INNER JOIN (SELECT MAX(timestamp) AS max_ts
FROM table2) t2
ON t.timestamp= t2.max_ts;

然而，使用您提供的示例表，这将不会返回任何结果，因为在table2

中没有找到sn 123。