选择表的内容，这是另一个值的结果 [英] SELECTing the contents of the Table, which is a result of another value

问题描述

我有五个桌子.

用户

+--------+----------+---------------+
| 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  |
+---------+-----------+

我对索引表中列出所有活动更感兴趣.因此，如果我给出这样的查询:

I am more interested in the Index table to list all the activities. So, if I give a query like this:

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

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

I would get all the contents like this:

+---------+-----------+------------+---------------------+------------+
| 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的主值.

If you see the result, the last column shows the Table Names and the concerned Primary Key (Item ID) of the table too. So, with the above result, I wanna add a column, that selects the main value from the table, with the ID specified.

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

In short, I would like the query to be:

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 是否可以?

推荐答案

使用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对其进行分隔

The better solution is to put the data from those different tables in one table and use the typeid to separate them

这篇关于选择表的内容，这是另一个值的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！