组合多个唯一的 MySQL 表并按一列排序 [英] Combine multiple unique MySQL tables and order by one column

问题描述

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

I've been trying to accomplish this MySQL query for the past few days now with very little luck. I'd like to combine these multiple tables and their columns, then order by one that they have in common (not by name, but by content). I have the following database tables:

mb_bans:

mb_bans:

mb_ban_records:

mb_ban_records:

mb_kicks:

mb_kicks:

mb_mutes:

mb_mutes:

mb_mutes_records:

mb_mutes_records:

mb_warnings:

mb_warnings:

我想要完成的是以下几行:

What I'm trying to accomplish is something among the lines of this:

不幸的是，我对如何将这些 MySQL 表组合在一起但同时将它们保存在不同的类别中感到非常困惑 - 按每个表中的 _time 列排序.我将如何处理这个问题?我尝试检索它们没有成功.. 我能得到的最接近的是组合每个列的 _time 列，然后在查询中给它一个值作为日期"，但是我不能对结果做太多事情.我仍然需要将其称为单独的行，对吗?我可能可以使用 fetchAll 但随后我将无法向值添加任何内容..

Unfortunately I am beyond stumped on how to combine these MySQL tables together but also at the same time keeping them in separate categories - ordered by the _time columns in each table. How would I approach this? I have been unsuccessful with my attempts at retrieving them.. The closest I could get was combining just the _time columns of each, then giving it a value in the query as "date", however I cannot do much with the results. I would still need to call it as the individual rows, correct? I could probably use fetchAll but then I would be unable to add anything to the values..

$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 表.任何可以帮助我的建议都非常感谢，因为我已经花了无数个小时试图自己解决这个问题.

Essentially I'd like to combine all of those queries together as one + the two _record tables. Any advice that could help me is greatly appreciated as I've spent countless hours trying to figure this out on my own.

提前致谢！

推荐答案

试试这个:

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表等)

how could I get the type of record? (IE. whether the returned result is from the bans table, mutes table, kicks table etc.)

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;

这篇关于组合多个唯一的 MySQL 表并按一列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！