below查询只需要很长时间,而below predicate 只用于获取唯一的记录,因此想知道是否有其他方法可以重写同一个查询而不多次调用below predicate 来获取唯一的id。
select max(c.id) from plocation c where c.ids = y.ids and c.idc = y.idc)
select max(cr.id) from plocation_log cr where cr.ids = yt.ids and cr.idc = yt.idc)
select max(pr.id) from patentpr where pr.ids = p.ids and pr.idc = p.idc)
我的完整示例查询
SELECT to_char(p.pid) AS patentid,
p.name,
x.dept,
y.location
FROM patent p
JOIN pdetails x ON p.pid = x.pid AND x.isactive = 1
JOIN plocation y
ON y.idr = p.idr
AND y.idc = p.idc
AND y.id = *(select max(c.id) from plocation c where c.ids = y.ids and c.idc = y.idc)*
AND y.idopstype in (36, 37)
JOIN plocation_log yt
ON yt.idr = y.idr
AND yt.idc= y.idc
AND yt.id = *(select max(cr.id) from plocation_log cr where cr.ids = yt.ids and cr.idc = yt.idc)*
AND yt.idopstype in (36,37)
WHERE
p.idp IN (10,20,30)
AND p.id = *(select max(pr.id) from patent pr where pr.ids = p.ids and pr.idc = p.idc)*
AND p.idopstype in (36,37)
2条答案
按热度按时间yuvru6vn1#
考虑加入聚合CTE以计算
MAX
每组值一次,而不是按行MAX
计算每个外部查询行。另外,请确保使用更多的信息表别名,而不是a, b, c
或者x, y, z
风格。06odsfpq2#
正如impaler所评论的,一种选择是使用分析函数而不是相关的子查询。其思想是使用
RANK()
,然后筛选外部查询(连接条件或WHERE
条款)。考虑: