如何写一个简单的自连接查询，通过比较它自己找到结果?

让我说实话，我是Java开发人员分配这个SQL开发工作，我需要列出所有的orderIdnodeName'Delay'和id"马克斯(id)">

节点名

使用相关子查询检查每个orderId的最后一个id是否有nodeName = 'Delay':

SELECT DISTINCT t1.orderId
FROM tablename t1
WHERE t1.nodeName = 'Delay'
AND t1.id = (SELECT MAX(t2.id) FROM tablename t2 WHERE t2.orderId = t1.orderId)

或:

SELECT DISTINCT t1.orderId
FROM tablename t1
WHERE 'Delay' = (
SELECT t2.nodeName 
FROM tablename t2 
WHERE t2.orderId = t1.orderId
ORDER BY id DESC LIMIT 1
)

或，带ROW_NUMBER()窗函数:

SELECT orderId
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY orderId ORDER BY id DESC) rn
FROM tablename
) t
WHERE rn = 1 AND nodeName = 'Delay'

我需要列出所有具有nodeName 'Delay'和id为'MAX(id)'的orderId

一种方法是使用having子句进行聚合:

select orderid
from t
group by orderid
having max(id) = max(case when nodename = 'Delay' then id end);

或者，如果您确实使用相关子查询，则会想到not exists:

select orderid
from t
where t.nodename = 'Delay' and
not exists (select 1
from t t2
where t2.orderid = t.orderid and
t2.nodename <> 'Delay' and
t2.id > t.id
);

假设您只搜索一行，试一试:

SELECT MAX(id), orderId, nodeName
FROM table_name
WHERE nodeName = 'Delay';

如果您不是只搜索一行，而是搜索具有最高id的结果，

试题:

SELECT *
FROM table_name
WHERE nodeName = 'Delay'
ORDER BY id DESC
LIMIT 5;

您可以使用以下查询

select t1.orderId
from tablename t1
join (SELECT MAX(id) as id FROM tablename group by orderId) t2 on t1.id = t2.id
where t1.nodeName = 'Delay'

相关内容

最新更新

热门标签：