如何在没有 IN 子句的情况下重写 SQL 查询

Question

Schema：

Table A: AID(PK), RECEIVE_DATE
Table B: BID(PK), AID(FK), MESSAGE, ITEMID, ITEMTYPE

表 A 到 B 具有一对多映射。

这是一个有效的SQL查询(在SQL Server中(，用于找出按ITEMID分组的最新消息，即针对不同的ITEMID(ITEMTYPE表示为"XYZ"(。

SELECT 
b.MESSAGE, b.ITEMID
from a
inner join b on b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
where a.receive_date in (select max(receive_date)
from a a1
inner join b b1 on b1.aid = a1.aid   
where b1.itemid = b.itemid
);

我们如何在没有IN子句[也没有使用行号概念]的情况下重写此SQL查询，因为ORACLE对IN子句有限制。获取 java.sql.SQLSyntaxErrorException： ORA-01795：对于上述表达式，列表中的最大表达式数为 1000。

Answer 1

我不清楚你为什么会ORA-01795. 子查询仅选择一个最大值，该最大值应该是单个值。 此外，1000 值限制仅适用于文本列表，而不适用于子查询。 在任何情况下，您都可以使用 join 而不是WHERE IN来改写此查询：

SELECT 
b.MESSAGE,
b.ITEMID
FROM a
INNER JOIN b
ON b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
INNER JOIN
(
SELECT
b1.itemid,
MAX(receive_date) AS max_receive_date
FROM a a1
INNER JOIN b b1
ON b1.aid = a1.aid   
GROUP BY b1.itemid
) t
ON b.itemid = t.itemid
WHERE a.receive_date = t.max_receive_date

Answer 2

EXISTS和IN往往是可以互换的，EXISTS在某些引擎(不确定Oracle(中表现更好，因为它在第一次匹配时返回true，而不是生成一个子集并对其进行检查。 我不熟悉 Oracle，但我想您可以使用以下内容来规避IN的 1000 行限制：

SELECT 
b.MESSAGE, b.ITEMID
from a
inner join b on b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
where exists (
SELECT 1
from a a1
inner join b b1 on b1.aid = a1.aid   
where b1.itemid = b.itemid
having MAX(a1.receive_date) = a.receive_date
)