mysql - SELECTing 表的内容，这是另一个值的结果

Question

我有五张 table 。

用户

+--------+----------+---------------+
| UserID | Username | Password      |
+--------+----------+---------------+
| 1      | Praveen  | Praveen       |
+--------+----------+---------------+
| 2      | Stack    | StackOverflow |
+--------+----------+---------------+
| 3      | CrazyGuy | OhMyGawd!     |
+--------+----------+---------------+

消息

+-----------+-------------+-----------+----------------------------------------------+
| MessageID | MessageFrom | MessageTo | MessageContent                               |
+-----------+-------------+-----------+----------------------------------------------+
| 1         | 1           | 2         | Hi Stack! Praveen here! :)                   |
+-----------+-------------+-----------+----------------------------------------------+
| 2         | 1           | 3         | Hey Crazy Guy, you are spamming me!!!        |
+-----------+-------------+-----------+----------------------------------------------+
| 3         | 2           | 3         | Hey, is Praveen speaking to you about spams? |
+-----------+-------------+-----------+----------------------------------------------+

评论

+-----------+--------+----------------------------------------+
| CommentID | UserID | CommentContent                         |
+-----------+--------+----------------------------------------+
| 1         | 1      | Hello! This is Praveen! Stop spamming! |
+-----------+--------+----------------------------------------+
| 2         | 1      | Hey CrazyGuy, stop your spams!!!       |
+-----------+--------+----------------------------------------+
| 3         | 3      | SPAM! SPAM!! SPAM!!!                   |
+-----------+--------+----------------------------------------+

索引表

+---------+-----------+------------+---------------------+
| IndexID | IndexType | IndexRowID | IndexTime           |
+---------+-----------+------------+---------------------+
| 1       | 1         | 1          | 2015-04-10 10:50:00 |
+---------+-----------+------------+---------------------+
| 2       | 1         | 2          | 2015-04-10 10:55:00 |
+---------+-----------+------------+---------------------+
| 3       | 2         | 1          | 2015-04-10 11:25:00 |
+---------+-----------+------------+---------------------+
| 4       | 3         | 1          | 2015-04-10 11:30:00 |
+---------+-----------+------------+---------------------+
| 5       | 2         | 2          | 2015-04-10 11:45:00 |
+---------+-----------+------------+---------------------+

表名

+---------+-----------+
| TableID | TableName |
+---------+-----------+
| 1       | Users     |
+---------+-----------+
| 2       | Messages  |
+---------+-----------+
| 3       | Comments  |
+---------+-----------+

我对列出所有事件的索引表更感兴趣。所以，如果我给出这样的查询:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName` FROM `IndexTable` ORDER BY `IndexTime` DESC;

我会得到这样的所有内容:

+---------+-----------+------------+---------------------+------------+
| IndexID | IndexType | IndexRowID | IndexTime           | IndexTable |
+---------+-----------+------------+---------------------+------------+
| 5       | 2         | 2          | 2015-04-10 11:45:00 | Messages   |
+---------+-----------+------------+---------------------+------------+
| 4       | 3         | 1          | 2015-04-10 11:30:00 | Comments   |
+---------+-----------+------------+---------------------+------------+
| 3       | 2         | 1          | 2015-04-10 11:25:00 | Messages   |
+---------+-----------+------------+---------------------+------------+
| 2       | 1         | 2          | 2015-04-10 10:55:00 | Users      |
+---------+-----------+------------+---------------------+------------+
| 1       | 1         | 1          | 2015-04-10 10:50:00 | Users      |
+---------+-----------+------------+---------------------+------------+

如果您看到结果，最后一列也会显示表名称和表的相关主键(项目 ID)。因此，对于上述结果，我想添加一列，从表中选择主要值，并指定 ID。

简而言之，我希望查询是:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, (
    SELECT {Username OR MessageContent OR CommentContent}
        FROM {`IndexTypeName`}
        WHERE {`UserID` OR `MessageID` OR `CommentID`} = `IndexRowID`
) AS `TableValue` FROM `IndexTable`
    ORDER BY `IndexTime` DESC;

MySQL-Server 可以吗？

Answer 1

在以下情况下使用 CASE:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, 
CASE 
    WHEN IndexType=1 THEN (SELECT Username FROM Users WHERE IndexRowID=UserID) 
    WHEN IndexType=2 THEN (SELECT MessageContent FROM Messages WHERE IndexRowID=MessageID) 
    WHEN IndexType=3 THEN (SELECT CommentContent FROM Comments WHERE IndexRowID=CommentID) END TableValue
ORDER BY `IndexTime` DESC;

更好的方案是将那些不同表的数据放在一张表中，用typeid分隔