sql - 如何在没有联合条款的情况下进行优化？

Question

是否可以在没有 union 子句的情况下编写以下查询。

select ProductId,ImageName,ImageType, ROW_NUMBER() over (order by ProductId desc) RowId 
from
                (
                    select p.id ProductId ,p.pic_image ImageName,'pic_image' ImageType
                    from product p
                    left outer join iimages_edited pe on p.id = pe.[id] 
                    where isnull(p.pic_image,'') <> '' and isnull(pe.pic_image,0)=0 
                    union
                    select p.id ProductId,p.pic_bimage ImageName,'pic_bimage' ImageType
                    from product p 
                    left outer join iimages_edited pe on p.id = pe.[id] 
                    where isnull(p.pic_bimage,'') <> '' and isnull(pe.pic_bimage,0)=0
                    union
                    select p.id ProductId,p.pic_limage ImageName,'pic_limage' ImageType
                    from product p 
                    left outer join iimages_edited pe on p.id = pe.[id] 
                    where isnull(p.pic_limage,'') <> '' and isnull(pe.pic_limage,0)=0 
                    union
                    select p.id ProductId,p.pic_blimage ImageName,'pic_blimage' ImageType
                    from product p 
                    left outer join iimages_edited pe on p.id = pe.[id] 
                    where isnull(p.pic_blimage,'') <> '' and isnull(pe.pic_blimage,0)=0 
                    union
                    select p.id ProductId,p.pic_cimage ImageName,'pic_cimage' ImageType
                    from product p 
                    left outer join iimages_edited pe on p.id = pe.[id] 
                    where isnull(p.pic_cimage,'') <> '' and isnull(pe.pic_cimage,0)=0       
            )t 

Above query has same table but different where condition, It is possible to do it in a single query ?

任何帮助将不胜感激!

提前致谢

Answer 1

您似乎每次都在使用不同的列重复相同的连接和过滤器。您可以使用 UNPIVOT 将它们转换为行，在每个表上，在连接之前:

select pe.ProductId, p.ProductId, p.ImageName, p.ImageType, ROW_NUMBER() 
over (order by p.ProductId desc) RowId 
from (
  select id as ProductId, ImageType, ImageName
  from product
  unpivot (
    ImageType for ImageName 
    in (pic_image, pic_bimage, pic_limage, pic_blimage, pic_cimage)
  ) t
) as p
left outer join (
  select id as ProductId, ImageType, ImageName
  from iimages_edited
  unpivot (
    ImageType for ImageName 
    in (pic_image, pic_bimage, pic_limage, pic_blimage, pic_cimage)
  ) t
) as pe
on p.ImageType = pe.ImageType
  and p.ProductId = pe.ProductId
where pe.ProductId is null

UNPIVOT 过滤 null 值，因此可能不需要 ISNULL。