SQL：帮助我优化我的SQL [英] SQL: Help me optimize my SQL

问题描述

我想要优化我的SQL。

I'm looking to optimize my SQL.

我的数据库模式是：

• home_id


• 地址


• 城市


• 州


• zip


• primary_photo_group_id

• home_id
• address
• city
• state
• zip
• primary_photo_group_id

• photo_id（主键）


• home_id / li>
  
• photo_group_id（照片组是相同的图片，从缩略图大小调整为大尺寸）


• home_photo_type_id（图片大小为缩略图a large size）


• photo_url_dir（存储照片的文件系统位置）

• photo_id (primary key)
• home_id (home primary key)
• photo_group_id (a photo group is the same image, resize from thumbnail to large size)
• home_photo_type_id (the size of the image be it a thumbnail or a large size)
• photo_url_dir (the filesystem location where the photo is stored)

很可能家没有与家庭相关联的照片。在这种情况下， primary_photo_group_id = 0 。

It's very possible that a 'home' does not have a photo associated with the home. In that case, the primary_photo_group_id = 0. Otherwise,primary_photo_group_id` equals the group_id of the photo to use as the primary photo.

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    photo_id,
    photo_url_dir
FROM homes, home_photos
WHERE homes.home_id = home_photos.home_id
AND primary_photo_group_id = home_photo_group_id
AND home_photo_type_id = 2

UNION

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    null,
    null
FROM homes
WHERE primary_photo_group_id = 0

想要做

我想摆脱UNION，因为我不得不搜索整个表2x。如何删除UNION，因为我需要检查primary_photo_group_id = 0的情况，如果它不等于0，然后查询 home_photos 表

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    photo_id,  (include only if primary_photo_group_id != 0)
    photo_url_dir  (include only if primary_photo_group_id != 0)
FROM homes,
    home_photos (include only if primary_photo_group_id != 0)
WHERE 
    primary_photo_group_id = 0
ELSE
    homes.home_id = home_photos.home_id
        AND primary_photo_group_id = home_photo_group_id
        AND home_photo_type_id = 2

推荐答案

也许你不知道左外连接？尝试：

Maybe you do not know about left outer join? Try:

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    photo_id
    photo_url_dir 
FROM homes h
left outer join home_photos hp on h.home_id = hp.home_id
    AND primary_photo_group_id = home_photo_group_id
    AND home_photo_type_id = 2

这篇关于SQL：帮助我优化我的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！