WITH test AS( SELECT 1 ID,1 NUM,1 box FROM dual UNION ALL SELECT 2,2,1 FROM dual UNION ALL SELECT 3,3,1 FROM dual UNION ALL SELECT 4,1,2 FROM dual UNION ALL SELECT 5,2,2 FROM dual) SELECT ID, NUM, BOX, (SELECT MAX(NUM) FROM TEST T1 WHERE T1.BOX = T.BOX) maxn FROM TEST T WHERE ID = 4 可以么?
例如 id= 5的结果为 4,1,2 , 2(这个箱子中最大的球号数为2)
SELECT 1 ID,1 NUM,1 box FROM dual UNION ALL
SELECT 2,2,1 FROM dual UNION ALL
SELECT 3,3,1 FROM dual UNION ALL
SELECT 4,1,2 FROM dual UNION ALL
SELECT 5,2,2 FROM dual)
SELECT ID, NUM, BOX, (SELECT MAX(NUM) FROM TEST T1 WHERE T1.BOX = T.BOX) maxn
FROM TEST T
WHERE ID = 4
可以么?