我有两个用于广告素材的表格及其缩略图。我想以类似的备用查询方式:

Initially, the thumbnail will be displayed for 300x250
IF not available then 336x280,
IF not available then 300x600,
IF not available then 728x90,
IF not available then 160x600,
IF not available then 320x50,
IF not available then 300x50,
IF not available then 468x60


表的结构就像

creatives:
id INT
name VARCHAR
desc TEXT

creative_thumbs:
id
creative_id INT
dimension VARCHAR
img_url VARCHAR


我通过多次联接(每个维度一个联接,但是速度有点慢)来实现

最佳答案

您可以使用多个LEFT JOIN查询来执行此操作。应该是这样的:

SELECT
    c.Name AS CreativeName,
    COALESCE(
        ct1.Dimension,
        ct2.Dimension,
        ct3.Dimension,
        ct4.Dimension,
        ct5.Dimension,
        ct6.Dimension,
        ct7.Dimension,
        ct8.Dimension
        ) AS ThumbnailDimension
FROM creatives c
LEFT JOIN creative_thumbs ct1 ON c.id = ct1.creative_id AND ct1.Dimension = '300x250'
LEFT JOIN creative_thumbs ct2 ON c.id = ct2.creative_id AND ct2.Dimension = '336x280'
LEFT JOIN creative_thumbs ct3 ON c.id = ct3.creative_id AND ct3.Dimension = '300x600'
LEFT JOIN creative_thumbs ct4 ON c.id = ct4.creative_id AND ct4.Dimension = '728x90'
LEFT JOIN creative_thumbs ct5 ON c.id = ct5.creative_id AND ct5.Dimension = '160x600'
LEFT JOIN creative_thumbs ct6 ON c.id = ct6.creative_id AND ct6.Dimension = '320x50'
LEFT JOIN creative_thumbs ct7 ON c.id = ct7.creative_id AND ct7.Dimension = '300x50'
LEFT JOIN creative_thumbs ct8 ON c.id = ct8.creative_id AND ct8.Dimension = '468x60'
;


如果这太慢,则尝试将索引添加到Dimension列:

CREATE INDEX creative_thumbs_Dimension_index
    on creative_thumbs (Dimension);

10-05 20:39
查看更多