带联接的SQL选择和需要的最后一条记录

|| 我有下表：

devices table
SN     tested_device_id  test_setup_id  

129    6      103  
129    7      104   
130    8      106

test_setup table
test_setup_id data1  
103 111  
104 333  
106 555

我想为每个实例的最后一个实例发送SN，test_setup_id，data1

serial number as follows:  
129,104,333   
130,106,555

（省略SN 129的早期副本）。我有一个查询，如下所示，该查询提供了SN 129的两个记录，并且不知道如何对其进行调节以仅发送它们中的最后一个：

SELECT sn, 
       test_setup_id, 
       data1 
FROM   test_setup 
       INNER JOIN devices 
         ON test_setup.test_setup_id = devices.test_setup_id

任何帮助都将受到欢迎。谢谢。

已邀请:

3 个回复

辩谷变充

尝试这个

SELECT d.sn, 
       d.test_setup_id, 
       ts.data1 
FROM   (SELECT d.sn, 
               MAX(d.test_setup_id) test_setup_id 
        FROM   devices d 
        GROUP  BY d.sn) maxsetupid 
       INNER JOIN devices d 
         ON maxsetupid.test_setup_id = d.test_setup_id 
       INNER JOIN test_setup ts 
         ON d.test_setup_id = ts.test_setup_id

藕挝

SELECT
  d.SN, d.test_setup_id, ts.data1
FROM devices d
  LEFT JOIN devices d2 ON d.SN = d2.SN and d.test_setup_id < d2.test_setup_id
  INNER JOIN test_setup ts ON d.test_setup_id = ts.test_setup_id
WHERE d2.SN IS NULL

苛肠倒俱

如果test_setup_id总是递增，则可以执行以下操作：

SELECT sn, 
       test_setup_id, 
       data1 
FROM   test_setup 
       INNER JOIN devices 
         ON test_setup.test_setup_id = devices.test_setup_id 
WHERE  test_setup_id = (SELECT MAX(test_setup_id) 
                        FROM   test_setup)

要回复问题请先登录或注册

带联接的SQL选择和需要的最后一条记录

3 个回复

发起人

max

sql

join

问题状态

带联接的SQL选择和需要的最后一条记录

与内容相关的链接

3 个回复

发起人

max

sql

join

问题状态