优化选择中的oracle多个子查询

Question

现在，我面临一个我认为可以优化的查询。

select i.itemID,
(select image from imagetable where imageType = 1 and itemID = i.itemID)
(select image from imagetable where imageType = 2 and itemID = i.itemID)
(select image from imagetable where imageType = 5 and itemID = i.itemID)
from 
    item i
where
    i.itemID = 3

这是一个需要优化的示例，问题是java将结果作为一行使用。如果我想要包含更多的“图像类型”，我需要包含更多的子查询。

所以问题是，我如何优化这个问题？

Answer 1

你的查询应该没问题。它甚至可能是最有效的方法，在imagetable(itemitem, imageType)上建立索引。

规范的替代方案是有条件的聚合：

select i.itemID,
       max(case when it.imageType = 1 then it.image end),
       max(case when it.imageType = 2 then it.image end),
       max(case when it.imageType = 5 then it.image end)
from item i left join
     imagetable it
     on it.itemId = i.itemId and it.imageType in (1, 2, 5)
where i.itemID = 3
group by i.itemId;

Answer 2

也许这是：

select i.itemID, t1.image im1, t2.image im2, t5.image im5
from item i
left join imagetable t1 on t1.itemId = i.itemId and t1.imageType=1
left join imagetable t2 on t2.itemId = i.itemId and t1.imageType=2
left join imagetable t5 on t5.itemId = i.itemId and t1.imageType=5
where i.itemID = 3

问题是，结果被java作为一行使用

这似乎非常严格，如果您的java接受多行，选择就会简单得多。但如果是这样的话。但是，我不确定它是否比您的选择更有效，我想(不尝试)也是一样的。(前两个子查询的结尾括号后面需要一个逗号)

以下内容也应该可以工作，可能取决于image数据类型：

select i.itemID, 
  max(decode(imageType,1,image)) im1,
  max(decode(imageType,2,image)) im2,
  max(decode(imageType,5,image)) im5
from item i left join imagetable t on t.itemId = i.itemId 
where i.itemID = 3
group by i.itemID

Answer 3

select i.itemID, 
case when imageType = 1 then image else null end, 
case when imageType = 2 then image else null end, 
case when imageType = 5 then image else null end
from 
    item i left join
 imagetable t
 on t.itemId = i.itemId 
where
    i.itemID = 3