mySQL查询——按日期排序

发布于 2024-10-13 15:54:43 字数 252 浏览 3 评论 0原文

我正在合并两个表，每个表都有不同的事件数据。每张桌子都有一个活动日期。

如何组合这些事件并按日期对它们进行无缝排序？

SELECT 
   t1.eventName, 
   t1.eventTime, 
   t2.evntName, 
   t2.evntTime
FROM t1
LEFT JOIN t2 ON (t1.eventID = t2.eventID)
ORDER BY ??? DESC
LIMIT 10

原文

I am merging two tables, each one with different events data. Each table has a date for the events.

How do I combine these events to sort them seamlessly by date?

SELECT 
   t1.eventName, 
   t1.eventTime, 
   t2.evntName, 
   t2.evntTime
FROM t1
LEFT JOIN t2 ON (t1.eventID = t2.eventID)
ORDER BY ??? DESC
LIMIT 10

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

累赘 2024-10-20 15:54:43

@Gonzalo 很接近，但还不够。您需要将每个表放入子查询中，并为最终结果中所需的每个列指定别名。如果一个表没有您需要从另一个表中获得的列，请使用文字并为其指定别名。

(SELECT eventName, eventTime, foo FROM t1)
UNION ALL
(SELECT evntName AS eventName, evntTime AS eventTime, NULL AS foo FROM t2)
ORDER BY eventTime DESC

将任何 WHERE 子句放入子查询内部。

@Gonzalo is close but not quite there. You need to put each table into subqueries and alias each column you want in the final result. If one table doesn't have a column that you need from another use a literal and alias it.

(SELECT eventName, eventTime, foo FROM t1)
UNION ALL
(SELECT evntName AS eventName, evntTime AS eventTime, NULL AS foo FROM t2)
ORDER BY eventTime DESC

Put any WHERE clauses inside the subqueries.

回复收藏 0 原文

小鸟爱天空丶 2024-10-20 15:54:43

不是 100% 确定您要在这里做什么，但也许是这样：

ORDER BY GREATEST(t1.eventTime, t2.eventTime) DESC

ETA：

看起来您无法直接将日期与 GREATEST 进行比较，因此您可能需要这样做：

ORDER BY GREATEST(UNIX_TIMESTAMP(t1.eventTime), UNIX_TIMESTAMP(t2.eventTime)) DESC

Not 100% sure what you are trying to do here, but maybe this:

ORDER BY GREATEST(t1.eventTime, t2.eventTime) DESC

ETA:

Looks like you can't compare dates directly with GREATEST, so you might need to do this:

ORDER BY GREATEST(UNIX_TIMESTAMP(t1.eventTime), UNIX_TIMESTAMP(t2.eventTime)) DESC

回复收藏 0 原文

一花一树开 2024-10-20 15:54:43

您正在连接这些表，这将导致两个表的记录合并在一行中。如果你真的想合并这样的表，你必须做的是 UNION 查询。像这样的事情：

SELECT eventName, eventTime FROM (
    SELECT eventName, eventTime
    FROM t1

    UNION

    SELECT eventName, eventTime
    FROM t2
)
ORDER BY eventTime

因此，您将两个表的结果合并在子查询中，然后对其进行排序。

祝你好运！

You're joining the tables, wich will result in records of both tables combined in one row. What you have to do if you really want to merge tables like that, is an UNION query. Something like this:

SELECT eventName, eventTime FROM (
    SELECT eventName, eventTime
    FROM t1

    UNION

    SELECT eventName, eventTime
    FROM t2
)
ORDER BY eventTime

So, you merge the results of both tables in a subquery, and the you sort it.

Good luck!

回复收藏 0 原文

~没有更多了~