在SQLAlchemy中使用ORDER BY和LIMIT的SQL子查询

Question

我需要用SQLAlchemy语言写这个查询。

SELECT * FROM Servers where Servers.protocol='TCP' and (
1=(SELECT status FROM Status WHERE Servers_ip = Servers.ip AND Servers_port = Servers.port ORDER BY timestamp desc LIMIT 1) OR 
4=(SELECT status FROM Status WHERE Servers_ip = Servers.ip AND Servers_port = Servers.port ORDER BY timestamp desc LIMIT 1)
)

我有一个名为Servers的类和一个名为Status的类，它们分别有表格中的属性。

提前谢谢你！

Answer 1

你需要把那些子查询放到另一个查询里面，这样才能得到一个派生表。否则在子查询中使用LIMIT是没用的。这样做就可以了：

SELECT 
    * 
FROM 
    Servers 
where Servers.protocol='TCP' and (
1=(
    SELECT s.status FROM (
        SELECT status 
        FROM Status 
        WHERE Servers_ip = Servers.ip AND Servers_port = Servers.port 
        ORDER BY timestamp desc 
        LIMIT 1
    ) s 
  ) 
OR 
4=(
    SELECT t.status FROM (
        SELECT status 
        FROM Status 
        WHERE Servers_ip = Servers.ip AND Servers_port = Servers.port 
        ORDER BY timestamp desc 
        LIMIT 1
    ) t
  ) 
);