选择表的内容,这是另一个值的结果
我有五个桌子.
用户
+--------+----------+---------------+
| UserID | Username | Password |
+--------+----------+---------------+
| 1 | Praveen | Praveen |
+--------+----------+---------------+
| 2 | Stack | * |
+--------+----------+---------------+
| 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