php - mysql 获取所有其他行如何从同一个表中获得相同的 id

Question

我有两张 table !

歌曲:

|---------------------|------------------|------------------|
|      Title          |         ID       |      Version     |
|---------------------|------------------|------------------|
|   songnumber1       |         34       |    original      |
|---------------------|------------------|------------------|
|   songnumber2       |         35       |      remix       |
|---------------------|------------------|------------------|
|   songnumber3       |         36       |      remix       |
|---------------------|------------------|------------------|

我有其他表来在歌曲之间进行引用引用:

|---------------------|------------------|------------------|
|         ID          |      remix       |      original    |
|---------------------|------------------|------------------|
|         1           |        35        |        34        |
|---------------------|------------------|------------------|
|         2           |        36        |        34        |
|---------------------|------------------|------------------|

我想要的是显示当前歌曲下面的相关歌曲假设 $id 是当前歌曲的 ID，即 35我想显示所有其他歌曲的混音版和原版我尝试了这个，但如果 $id 是混音，它只会得到原始的:

SELECT * FROM reference
         INNER JOIN songs ON reference.remix = songs.id OR 
reference.original = songs.id
         WHERE reference.original = '$id' OR reference.remix = '$id' 

Answer 1

试试这个:

SELECT 
  C.currently_playing, 
  GROUP_CONCAT(DISTINCT IF(C.currently_playing=C.id,C.title,null)) current_song_title, 
  GROUP_CONCAT(DISTINCT IF(C.version='original',C.id,null)) orignial_song_id,
  GROUP_CONCAT(DISTINCT IF(C.version='original',C.title,null)) orignial_song_title, 
  GROUP_CONCAT(DISTINCT IF(C.version='remix',C.id,null)) related_remix_ids, 
  GROUP_CONCAT(DISTINCT IF(C.version='remix',C.title,null)) related_remix_titles 
FROM
  (SELECT 35 AS currently_playing, A.*
   FROM Songs   A 
   RIGHT JOIN (SELECT * FROM `references` WHERE remix=35 or original=35) B
   ON A.id IN (B.original,B.remix)) C
GROUP BY C.currently_playing;

查看工作 Demo on SQL Fiddle .