請原諒我，因為我是 mysql 的新手。我有 4 個表格，其中包含有關 Song、Remix、Release 和 Project 的資訊，如下所示：
歌曲 song_id, song_year_released, project_id
混音 remix_id, remix_year_released, project_id
發布 release_id, release_year, project_id
專案 project_id, project_name
我想從 1995 年發布的多個表中獲取所有 project_id。在同一個查詢中，我想加入Project表并從結果中獲取所有 project_id 的專案名稱。
我已經設法通過 UNION 使用以下查詢獲取專案 ID：
SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995'
我將如何加入 Project 表以便從上述聯合中獲取每個 project_id 的 project_name？我嘗試了下面的代碼，但當然它不起作用。
SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995'
UNION
SELECT p.project_name FROM `Project` p
INNER JOIN `Project` p ON p.project_id = s.project_id
先感謝您。
uj5u.com熱心網友回復：
使用 mysql 8 你可以做到。
正如我的評論中提到的，MySQL 沒有FULL OUTER JOIN，但你可以用 cqan 模擬它
WITH CTE AS (SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995')
SELECT p.*,CTE.* FROM `Project` p LEFT JOIN CTE s ON p.project_id = s.project_id
UNION
SELECT p.*,CTE.* FROM CTE s LEFT JOIN `Project` p ON p.project_id = s.project_id
對于早期版本，您需要用 SELECT 孔替換 CTE
但這總是很慢，當然取決于資料庫的大小
我看到你改變了你的查詢：
以上查詢的邏輯仍然有效
WITH CTE AS (SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995')
SELECT p.project_name FROM `Project` p INNER JOIN CTE s ON p.project_id = s.project_id
在早期版本的 MySQL 中
你必須做以下查詢
SELECT p.project_name FROM `Project` p INNER JOIN (SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995') s ON p.project_id = s.project_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/534227.html
標籤：数据库数据库加入选择联盟
上一篇：安裝Xamarin.Google.Android.ODML.Image-Version1.0.0.2-beta1后，Xamarinandroid專案無法構建