mysql - 如何在查询时避免笛卡尔积？

Question

我正在构建一个分类广告网络应用程序(这是我的第一个专业 PHP 应用程序)。我有一张包含广告的表格，还有一张包含每个广告图片的表格。每个广告最多可包含 4 张图片。当我为每个广告只添加一张图片时，一切都很好。现在，当我为每个广告转换多张图片时，该广告会在列表中重复(每张图片重复一次)。这是我正在使用的 SQL:

SELECT 
sun_classified_ads.ad_id, 
sun_classified_ads.insertion_datetime, 
sun_classified_ads.title,                
sun_classified_ads.description, 
sun_classified_ads.maker, 
sun_classified_ads.model, 
sun_classified_ads.location, 
sun_classified_ads.price, 
sun_classified_ads_images.filename_mini, 
sun_classified_ads_images.size_attr_mini, 
sun_classified_ads_images.description AS image_description
FROM sun_classified_ads
INNER JOIN sun_classified_ads_images ON sun_classified_ads.ad_id =       sun_classified_ads_images.ad_id

我怎样才能让它只使用 sun_classified_ads_images 表中该广告的第一张图片？

Answer 1

MySQL 有一个叫做隐藏列的“特性”。您可以使用以下方法为每个广告获取一张图片:

SELECT 
sun_classified_ads.ad_id, 
sun_classified_ads.insertion_datetime, 
sun_classified_ads.title,                
sun_classified_ads.description, 
sun_classified_ads.maker, 
sun_classified_ads.model, 
sun_classified_ads.location, 
sun_classified_ads.price, 
sun_classified_ads_images.filename_mini, 
sun_classified_ads_images.size_attr_mini, 
sun_classified_ads_images.description AS image_description
FROM sun_classified_ads
INNER JOIN sun_classified_ads_images ON sun_classified_ads.ad_id =       sun_classified_ads_images.ad_id
group by sun_classified_ads.ad_id

只是一个警告。无法保证这会返回第一个 广告。不能保证图像中的三个字段实际上来自同一个记录(尽管实际上它们确实如此)。但是，这可能是获得所需内容的最简单方法。

以下查询确实可以满足您的要求，但工作量更大:

SELECT 
sun_classified_ads.ad_id, 
sun_classified_ads.insertion_datetime, 
sun_classified_ads.title,                
sun_classified_ads.description, 
sun_classified_ads.maker, 
sun_classified_ads.model, 
sun_classified_ads.location, 
sun_classified_ads.price, 
substring_index(group_concat(sun_classified_ads_images.filename_mini order by ad_image_id), ',', 1), 
substring_index(group_concat(sun_classified_ads_images.size_attr_mini order by ad_image_id), ',', 1), 
substring_index(group_concat(sun_classified_ads_images.description order by ad_image_id), ',', 1) AS image_description
FROM sun_classified_ads
INNER JOIN sun_classified_ads_images ON sun_classified_ads.ad_id =       sun_classified_ads_images.ad_id
group by sun_classified_ads.ad_id

这假定 ad_image_id 是一个可用于定义图像顺序的字段。