mysql组并对UNION进行排序
In my guestbook I have 2 tables: messages
and replies
. Now, I want to get all messages grouped by id (means that message and corresponding replies will be grouped/together) and sorted by date DESC (the newest messages will be first; if a message is the oldest one, but the corresponding reply is the newest of all messages, this group will be on the top of the table), while replies will be sorted by date ASC (oldest reply on the top). Here my mysql query that works good except it doesnt sort replies by date ASC
在我的留言簿中,我有2个表:消息和回复。现在,我希望获取按ID分组的所有消息(意味着消息和相应的回复将被分组/一起)并按日期DESC排序(最新的消息将是第一个;如果消息是最早的消息,但相应的回复是所有消息中的最新消息,该组将位于表的顶部),而回复将按日期ASC排序(最顶层的回复)。这里我的mysql查询运行良好,除了它没有按日期ASC排序回复
SELECT msg.id as id, msg.comment, msg.date_added as date_added, 0 as is_reply
FROM messages AS msg
UNION
SELECT reply.msg_id as id, reply.comment, reply.date_added as date_added, 1 as is_reply
FROM pg_reply as reply
GROUP BY id
ORDER BY date_added DESC, is_reply ASC
is_reply ASC
doesnt do the job as I supposed
is_reply ASC不像我想的那样完成这项工作
reply.msg_id
specifies id of reply's parent (messages.id
)
reply.msg_id指定回复父的id(messages.id)
What the result should look like>
结果应该是什么样的>
- message A
- oldest reply B
- old reply C
- new reply Z // this is the newest message in the guestbook
- newer message E // is newer than A but older than the newest message in the guestbook, which is Z
- reply F // (this reply is newer than all messages but message Z)
4 个解决方案
#1
2
For this answer I'm going to assume that reply.msg_id is a linkfield to the original message.
对于这个答案,我将假设reply.msg_id是原始消息的链接字段。
SELECT id, comment, date_added, is_reply FROM (
SELECT
msg.id as id
, msg.comment
, msg.date_added as date_added
, 0 as is_reply
FROM messages AS msg
UNION
SELECT
reply.msg_id as id
, reply.comment
, reply.date_added as date_added
, 1 as is_reply
FROM pg_reply as reply ) AS allmsg
ORDER BY id DESC, is_reply, date_added DESC
This works assuming that msg_id
is an autoincrement field and that newer id's also have a newer date_added
timestamp.
这可以假设msg_id是一个自动增量字段,而较新的id也有一个较新的date_added时间戳。
Remarks on the original code
In your original code you have
关于原始代码的备注在您的原始代码中
GROUP BY id
ORDER BY date_added DESC, is_reply ASC
The GROUP BY
implicitly orders on id
ASC; the following ORDER BY
overrides that and orders by date_added
first and is_reply
second.
However if date_added
is a datetime
then the chance of two post having the same time are remote (esp. for replies, it takes time to write them),
so the 2nd order clause hardly ever gets used.
GROUP BY隐含地在id ASC上订购;以下ORDER BY首先覆盖date_added和is_reply秒的命令和命令。但是如果date_added是一个日期时间,则两个帖子具有相同时间的可能性是远程的(特别是对于回复,它需要时间来编写它们),因此第二个订单子句几乎不会被使用。
GROUP BY
should only be used if you have an aggregate function in your select
, such as SUM
or COUNT
只有在select中有聚合函数时才应使用GROUP BY,例如SUM或COUNT
If you want to remove duplicates from your select do not use group by, use distinct as in select distinct a,b,c from table1 where ...
如果你想从你的选择中删除重复项,请不要使用group by,使用distinct作为select table a,b,c from table1 where ...
更多相关文章
- 如何从mysql datetime列返回转换后的时间格式?
- 如何将休眠时间戳映射到MySQL BIGINT?
- 为什么这个查询需要很长时间才能执行
- 在时间戳和相应的值中选择每天的最后一个条目。
- 如何在php中测量mysql时间,sql查询的时间和/或负载?
- MySQL - 更改一行的时间值以匹配同一表的另一行的时间值
- MySQL学习笔记_时间,多表更新,数据库元数据
- 函数的作用是:在javascript中将时间戳转换为人工日期
- js和php时间戳的问题