SQL-如何在案例中加入-有解無憂

這是根據型別/值將表連接到不同表的正確方法嗎？

在我的表中，我有一列geo可以是1, 2, 或3 and geo_id。基于geo's type 我想加入另一個表。到目前為止，唯一的解決方案是使用union.

  SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    cntr.countryName as geo_name
FROM
    tbl_documents doc
JOIN 
    tbl_countries cntr ON doc.geo_id = cntr.country_Id
WHERE doc.geo = 1

UNION ALL

SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    reg.regionName as geo_name
FROM
    tbl_documents doc
JOIN 
    tbl_regions reg ON doc.geo_id = reg.region_Id
WHERE 
    doc.geo = 2

UNION ALL

SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    area.areaName as geo_name
FROM
    tbl_documents doc
JOIN 
    tbl_areas area ON doc.geo_id = area.area_Id
WHERE doc.geo = 3
ORDER BY document_id

uj5u.com熱心網友回復：

您還可以LEFT JOIN：

SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    coalesce(cntr.countryName, reg.regionName, area.areaName) as geo_name
FROM
    tbl_documents doc
LEFT JOIN 
    tbl_countries cntr ON doc.geo_id = cntr.country_Id AND doc.geo = 1
LEFT JOIN 
    tbl_regions reg ON doc.geo_id = reg.region_Id AND doc.geo = 2
LEFT JOIN 
    tbl_areas area ON doc.geo_id = area.area_Id AND doc.geo = 3
ORDER BY doc.document_id

uj5u.com熱心網友回復：

左連接每個地理表，然后使用 COALESCE 從地理表中選擇第一個非空值：

  SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    COALESCE(cntr.countryName, reg.regionName, area.areaName) AS geo_name
FROM
    tbl_documents doc
LEFT JOIN 
    tbl_countries cntr 
    ON doc.geo_id = cntr.country_Id
    AND doc.geo = 1
LEFT JOIN 
    tbl_regions reg 
    ON doc.geo_id = reg.region_Id
    AND doc.geo = 2
LEFT JOIN 
    tbl_areas area 
    ON doc.geo_id = area.area_Id
    AND doc.geo = 3

uj5u.com熱心網友回復：

將表聯合到單個源中以進行連接，添加一個附加列以指定源/地理型別并包含在您的連接中。

正如您所做的那樣，它實際上與合并三個查詢相同，但減少了代碼重復量。

SELECT 
  doc.document_id,
  doc.documentTitle,
  doc.documentUrl,
  doc.created_at,
  doc.delete_date,
  label.name as geo_name
FROM
  tbl_documents   AS doc
LEFT JOIN
(
  SELECT 1 as geo, country_id AS id, countryName AS name FROM tbl_countries
  UNION ALL
  SELECT 2 as geo, region_id  AS id, regionName  AS name FROM tbl_regions
  UNION ALL
  SELECT 3 as geo, area_id    AS id, areaName    AS name FROM tbl_areas
)
  AS label
    ON  label.geo = doc.geo
    AND label.id  = doc.geo_id
ORDER BY
  doc.document_id

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/438135.html

標籤：mysql sql

上一篇：如何將散列密碼存盤到資料庫？

下一篇：OrderBy在左外連接內給出隨機結果