SQL 子选择作为字段？

Question

我在这里有点迷失...

我有几个表想从中提取统一记录：Unit、Building、Owner 和图片。

到目前为止，这是我的查询：

SELECT building.`Street_Address`
    , building.`Building_Name`
    , building.`Building_Type`
    , CONCAT(building.`Cross_Street_1`, ' & ', building.`Cross_Street_2`) Cross_Streets
    , building.`Cross_Street_1`
    , building.`Cross_Street_2`
    , building.`Access` Building_Access
    , owner.`Company_Name`
    , owner.`Contact_Or_Reference`
    , owner.`Landlord_Phone`
    , picture.`Path_To_Picture_On_Server`
    , picture.`Picture_Category`
    , unit.`Apartment_Number`
    , unit.`Unit_Size_Number` Size
    , unit.`Is_Doorman`
    , unit.`Is_Furnished`
    , unit.`Is_Elevator`
    , unit.`Is_Pets`
    , unit.`Is_OutdoorSpace`
    , unit.`Rent_Price`
    , unit.`Baths`
    , unit.`Access` Unit_Access
    , unit.`fourd_id`
    , unit.`Updated_Date`
    , unit.`Occupancy_Date`
    , unit.`Term`
    , unit.`Incentives`
    , unit.`Info_OutdoorSpace`
    , unit.`List_Date`
    , zone.`Description`
FROM 4D_Units unit
JOIN 4D_Building building
    ON unit.`BUILDING_RecID` = building.`fourd_id`
JOIN 4D_Zones zone
    ON building.`ZONES_RecID` = zone.`fourd_id`
LEFT JOIN 4D_Owners owner
    ON unit.`OWNER_RecID` = owner.`fourd_id`
LEFT JOIN 4D_Building_Picts picture
    ON (building.`fourd_id` = picture.`BUILDING_RecID` AND picture.`Picture_Category` = 'Front')
WHERE unit.`id` = 49901

这按原样工作正常，除了返回记录在记录中仅包含“正面”图片（如果存在）。我的问题是，有几种不同类型的照片可能与返回记录相关联，包括“全景”、“室内”和“平面图”...对于 picture.Picture_Category.

有没有办法在返回的集合中返回这些值（如果它们存在，如上所述），而无需执行单独的查询？我希望返回的集合包含picture.Picture_Category所有四个可能选项的别名值（如果存在）：“Front”、“Panorama”、“Interior”和“Interior”。 “平面图”（具有与其关联的唯一picture.Path_To_Picture_On_Server）。

这有道理吗？

Answer 1

如果我理解正确的话，您希望在结果集中有 4 组图片列 - 4 个类别之一？现在你前面只有一张，对吧？

您可以使用不同的别名和不同的连接子句多次连接到同一个表。只需加入 4D_Building_Picts 4 次，每张您想要的图片一次。

select 
    --whatever
    , pic_front.`Path_To_Picture_On_Server` AS Front_Path_To_Picture_On_Server
    , pic_panorama.`Path_To_Picture_On_Server` AS Panorama_Path_To_Picture_On_Server
    , pic_interior.`Path_To_Picture_On_Server` AS Interior_Path_To_Picture_On_Server
    , pic_floorplan.`Path_To_Picture_On_Server` AS Floorplan_Path_To_Picture_On_Server
    --whatever
FROM 4D_Units unit
JOIN 4D_Building building
    ON unit.`BUILDING_RecID` = building.`fourd_id`
JOIN 4D_Zones zone
    ON building.`ZONES_RecID` = zone.`fourd_id`
LEFT JOIN 4D_Owners owner
    ON unit.`OWNER_RecID` = owner.`fourd_id`
LEFT JOIN 4D_Building_Picts pic_front
    ON (building.`fourd_id` = pic_front.`BUILDING_RecID` AND pic_front.`Picture_Category` = 'Front')
LEFT JOIN 4D_Building_Picts pic_panorama
    ON (building.`fourd_id` = pic_panorama.`BUILDING_RecID` AND pic_panorama.`Picture_Category` = 'Panorama')
LEFT JOIN 4D_Building_Picts pic_interior
    ON (building.`fourd_id` = pic_interior.`BUILDING_RecID` AND pic_interior.`Picture_Category` = 'Interior')
LEFT JOIN 4D_Building_Picts pic_floorplan
    ON (building.`fourd_id` = pic_floorplan.`BUILDING_RecID` AND pic_floorplan.`Picture_Category` = 'Floorplan')
WHERE unit.`id` = 49901

Answer 2

我认为你想要合并功能。它需要多个字段，并返回其中第一个非空字段。所以就像这样：

Select
  Coalesce(A.Panorama, A.Interior, A.Floorplan, '') as ImagePath
From
  Table A

尽管如此，您最终只能得到一个值，但这实际上可能不是您想要的。如果您想要所有这些，我建议使用相关子查询，如下所示：

Select
  (Select P.Path_To_Picture From 4D_Building_Picts P where P.Building_RecID = B.fourd_Id And P.Picture_Category = 'Front') as Front_Pic,
  (Select P.Path_To_Picture From 4D_Building_Picts P where P.Building_RecID = B.fourd_Id And P.Picture_Category = 'Panorama') as Panamora_Pic,
  (Select P.Path_To_Picture From 4D_Building_Picts P where P.Building_RecID = B.fourd_Id And P.Picture_Category = 'FloorPlan') as FloorPlan_Pic,
  ...
From
  4D_Building B