连接两个表，然后按日期排序，但将两个表合并 [英] Join two tables, then Order By date, BUT combining both tables

问题描述

好的，我试图弄清楚为什么我不明白如何做到这一点……

Alright, I'm trying to figure out why I can't understand how to do this well...

我有两个表:

• 发票:

• id
• 用户ID
• 金额
• 日期

付款:

• id
• 用户ID
• 金额
• 日期

所以，这里的目标是连接两个表，其中userID匹配我想要的任何内容-然后返回按日期排序的所有内容(最新的在顶部).但是，由于每个表中都有一个日期字段，所以我不确定MySQL将如何处理...会自动按两个日期排序吗?这就是我的想法...

So, the goal here is to join both tables, where the userID matches whatever I want it to be - and then return everything ordered by date (most recent at the top). However, because there is a date field in each of the tables, I'm not sure how MySQL will handle things... will is sort by both dates automatically? Here's what I was thinking...

"SELECT DISTINCT *
            FROM invoices,payments
            WHERE {$userID} = invoice.userID
            OR {$userID} = payments.userID
            ORDER BY date DESC";

但是，对我来说，开始变得很清楚，也许这甚至不是对join命令的正确使用...也许我只需要单独获取每个表上的所有数据，然后尝试使用PHP对其进行排序?如果那是更好的方法，那么在保持所有行数据不变的情况下进行这种DATE排序的好方法是什么?

But, it's starting to become clear to me that maybe this isn't even the right use of a join command... maybe I need to just get all data on each table alone, then try to sort it somehow with PHP? If that's the better method, what's a good way to do this type of DATE sort while keeping all row data in tact?

我应该补充一下，unix时间戳中的TIME(即日期"的存储方式)不可忽略-它应该按日期和时间排序.

I should add, the TIME inside the unix timestamp (that's how "date" is stored) is NOT negligible - it should sort by the date and time.

谢谢...

推荐答案

如果两个表的列都相同，则可以使用UNION

If the columns of both tables are the same, you can use a UNION

SELECT X.*
  FROM ( SELECT `id`,
                `userID`,
                 'INVOICE' AS PTYPE
                 `amount`,
                 `date`
            FROM `invoices`
           WHERE {$userID} = userID  
          UNION
          SELECT `id`,
                 `userID`,
                 'PAYMENT' AS PTYPE
                 `amount`,
                 `date`
            FROM `payments`
           WHERE {$userID} = userID  
        ) X
 ORDER BY X.`date`

编辑

在UNIONS上阅读 MySQL手册的相关部分.还有其他措辞措词，但这是我的首选样式-任何人都应该清楚ORDER BY子句适用于UNION的双方两面的结果.粗心编写的UNION-即使使用ORDER BY-仍可能使最终结果集的顺序不确定.

Read the relevant section of the MySQL manual on UNIONS. There are other ways of phrasing this, but this is my preferred style - it should be clear to anybody reading that the ORDER BY clause applies to the result of both sides of the UNION. A carelessly written UNION - even with an ORDER BY - may still leave the final resultset in indeterminate order.

PTYPE的目的是该查询返回一个称为PTYPE的额外列，该列指示每个单独的行是INVOICE还是PAYMENT....它来自两个表中的哪个.它不是强制性的，但在工会中通常很有用

The purpose of the PTYPE is that this query returns an extra column called PTYPE, that indicates whether each individual row is an INVOICE or a PAYMENT... ie. which of the two tables it comes from. It's not mandatory, but can often be useful within a union

这篇关于连接两个表，然后按日期排序，但将两个表合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！