我正在嘗試使用 JOIN 和 group_concat 從 4 個表中回傳資料
這是我當前的查詢：
SELECT m.ID AS num
, m.Name AS MOVIE
, group_concat(v.Codec, '-') AS v_CODEC
, group_concat(a.Codec, '-') AS a_CODEC
, group_concat(s.Language, '-') AS SUBs
FROM File AS m
LEFT JOIN Video AS v ON m.ID = v.FileID
LEFT JOIN Audio AS a ON m.ID = a.FileID
LEFT JOIN Subtitle AS s ON m.ID = s.FileID
GROUP BY m.ID
ORDER BY m.ID
使用我當前的查詢，我的結果如下所示：
----- ---------- ---------------- ------------- ----------
| num | MOVIE | v_CODEC | a_CODEC | SUBs |
----- ---------- ---------------- ------------- ----------
| 1 | Superman | H264-H264-H264 | DTS-DTS-DTS | en-en-en |
| | | H264-H264-H264 | DTS-DTS-DTS | en-en-en |
----- ---------- ---------------- ------------- ----------
| 2 | Batman | MPEG2-MPEG2 | MP3-MP3 | |
----- ---------- ---------------- ------------- ----------
我希望我的結果是這樣的：
----- ---------- --------- ------------- -------
| num | MOVIE | v_CODEC | a_CODEC | SUBs |
----- ---------- --------- ------------- -------
| 1 | Superman | H264 | DTS-DTS-DTS | en-en |
----- ---------- --------- ------------- -------
| 2 | Batman | MPEG2 | MP3-MP3 | |
----- ---------- --------- ------------- -------
這是我的資料庫結構：
File
---- ----------
| ID | Name |
---- ----------
| 1 | Superman |
---- ----------
| 2 | Batman |
---- ----------
Video
-------- -------
| FileID | Codec |
-------- -------
| 1 | H264 |
-------- -------
| 2 | MPEG2 |
-------- -------
Audio
-------- -------
| FileID | Codec |
-------- -------
| 1 | DTS |
-------- -------
| 1 | DTS |
-------- -------
| 1 | DTS |
-------- -------
| 2 | MP3 |
-------- -------
| 2 | MP3 |
-------- -------
Subtitle
-------- ----------
| FileID | Language |
-------- ----------
| 1 | en |
-------- ----------
| 1 | en |
-------- ----------
如何使我的查詢達到我所需要的？
預先感謝您的幫助。
uj5u.com熱心網友回復：
我相信以下是您想要的：-
SELECT
m.ID AS num,
m.name AS movie,
(SELECT group_concat(codec,'-') FROM Video WHERE FileID = m.ID) AS v_codec,
(SELECT group_concat(codec,'-') FROM Audio WHERE FileID = m.ID) AS a_codec,
(SELECT group_concat(Language,'-') FROM Subtitle WHERE FileID = m.ID) AS SUBs
FROM File AS m ORDER BY m.id;
產生：-
那不是將提取物基于笛卡爾積，而不是分組，而是類似于：-
答案僅在關系上執行 group_concat
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/355912.html