时间戳

首页 > 解决方案 > mysql 如果第一次不存在则回退到另一条记录

mysql - mysql 如果第一次不存在则回退到另一条记录

问题描述

我有两个用于广告素材及其缩略图的表格。我想以一种后备方式查询，例如：

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

我已经使用多个连接（每个维度一个，但速度有点慢）

标签： mysqljoinbackground-imagefallbackmultiple-join-rows

解决方案

您可以使用多个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);

推荐阅读