php - 组合多个唯一的 MySQL 表并按一列排序

Question

过去几天我一直在尝试完成此 MySQL 查询，但运气不佳。我想合并这些多个表及其列，然后按它们共有的一个(不是按名称，而是按内容)排序。我有以下数据库表:

mb_bans:

mb_ban_records:

mb_kicks:

mb_静音:

mb_mutes_records:

mb_警告:

我想要完成的事情是这样的:

不幸的是，我无法理解如何将这些 MySQL 表组合在一起，同时又将它们保持在不同的类别中——按每个表中的 _time 列排序。我将如何处理这个？我试图检索它们但没有成功。我能得到的最接近的结果是只组合每个列的 _time 列，然后在查询中给它一个值作为“日期”，但是我不能对结果做太多事情。我仍然需要将其称为单独的行，对吗？我可能可以使用 fetchAll，但那样我将无法向值添加任何内容..

$banq = $db->prepare('SELECT banned, banned_by, ban_reason, ban_time, ban_expires_on FROM '.BAN_TABLE.' ORDER BY ban_time DESC');
$kickq = $db->prepare('SELECT kicked, kicked_by, kick_reason, kick_time FROM '.KICK_TABLE.' ORDER BY kick_time DESC');
$muteq = $db->prepare('SELECT muted, muted_by, mute_reason, mute_time, mute_expires_on FROM '.MUTE_TABLE.' ORDER BY mute_time DESC');
$warnq = $db->prepare('SELECT warned, warned_by, warn_reason, warn_time FROM '.WARN_TABLE.' ORDER BY warn_time DESC');
$banq->execute();
$kickq->execute();
$muteq->execute();
$warnq->execute();

基本上，我想将所有这些查询合并为一个 + 两个 _record 表。非常感谢任何可以帮助我的建议，因为我花了无数个小时试图自己解决这个问题。

提前致谢!

Answer 1

试试这个:

SELECT * from (
SELECT banned as Punisher, banned_by as Punished, ban_reason as Reason, ban_expires_on  as Expire, ban_time as Date  FROM mb_bans 
UNION
SELECT kicked as Punisher, kicked_by as Punished, kick_reason as Reason, NULL  as Expire, kick_time as Date FROM mb_kicks 
UNION
SELECT muted as Punisher, muted_by as Punished, mute_reason as Reason, mute_expires_on  as Expire, mute_time as Date  FROM mb_mutes 
UNION
SELECT warned as Punisher, warned_by as Punished, warn_reason as Reason, NULL  as Expire,  warn_time as Date FROM mb_warnings
) d order by d.Date DESC;

编辑

如何获取记录的类型？(即返回结果是否来自 bans 表、mutes 表、kicks 表等)

SELECT * from (
    SELECT banned as Punisher, banned_by as Punished, ban_reason as Reason, ban_expires_on  as Expire, 'ban' as TableType, ban_time as Date  FROM mb_bans 
    UNION
    SELECT kicked as Punisher, kicked_by as Punished, kick_reason as Reason, NULL  as Expire, 'kick' as TableType, kick_time as Date FROM mb_kicks 
    UNION
    SELECT muted as Punisher, muted_by as Punished, mute_reason as Reason, mute_expires_on  as Expire, 'mute' as TableType, mute_time as Date  FROM mb_mutes 
    UNION
    SELECT warned as Punisher, warned_by as Punished, warn_reason as Reason, NULL  as Expire,  'warn' as TableType, warn_time as Date FROM mb_warnings
    ) d order by d.Date DESC;