我有这样一张桌子:
idstable
+--------+-----+-----+-----+-----+-----+
| userid | id1 | id2 | id3 | id4 | id5 |
+--------+-----+-----+-----+-----+-----+
| 1 | 6 | 2 | 52 | 32 | 16 |
+--------+-----+-----+-----+-----+-----+
| 2 | 12 | 5 | 18 | 21 | 5 |
+--------+-----+-----+-----+-----+-----+
| ... | ... | ... | ... | ... | ... |我想要获取存储在列中的id号码并从其他表中获取行。我希望从中得到行的表是:
namestable
+----+----------+
| id | name |
+----+----------+
| 6 | Bruce |
+----+----------+
| 2 | Mary |
+----+----------+
| 52 | Dick |
+----+----------+
| 32 | Bob |
+----+----------+
| 16 | Jack |
+----+----------+
| .. | ... |我试图得到的结果集是这样的:
+------+----+-------+
| User | id | name |
+------+----+-------+
| 1 | 6 | Bruce |
+------+----+-------+
| 1 | 2 | Mary |
+------+----+-------+
| 1 | 52 | Dick |
+------+----+-------+
| 1 | 32 | Bob |
+------+----+-------+
| 1 | 16 | Jack |
+------+----+-------+我如何查询idstable与用户ID检索与它相关的ID,然后从namestable检索正确的行?我读过这个称为转置,但我还没有找到一个简洁的例子。
有任何想法吗?