A customer has asked me to pull some extra accounting data for their statements. I have been having trouble writing a query that can accomodate this.
一位客户要求我为他们的报表提取一些额外的会计数据。我在编写一个可以容纳这个的查询时遇到了麻烦。
What they initially wanted was to get all "payments" from a certain date range that belongs to a certain account, ordered as oldest first, which was a simple statement as follows
他们最初想要的是，从属于某个账户的某个日期范围内得到所有“付款”，这是最古老的，这是一个简单的声明如下!
SELECT * FROM `payments`
WHERE `sales_invoice_id` = ?
ORDER_BY `created_at` ASC;
However, now they want to have newly raised "invoices" as part of the statement. I cannot seem to write the correct query for this. Union does not seem to work, and JOINS behave like, well, joins, so that I cannot get a seperate row for each payment/invoice; it instead merges them together. Ideally, I would retrieve a set of results as follows:
然而，现在他们希望将新增加的“发票”作为声明的一部分。我似乎无法为它编写正确的查询。Union似乎不起作用，join的行为类似于join，因此我不能为每个付款/发票获得一个独立的行;而是把它们合并在一起。理想情况下，我将检索如下所示的一组结果:
payment.amount | invoice.amount | created_at
-----------------------------------------------------------
NULL | 250.00 | 2014-02-28 8:00:00
120.00 | NULL | 2014-02-28 8:00:00
This way I can loop through these to generate a full statement. The latest query I tried was the following:
通过这种方式，我可以循环这些语句来生成一个完整的语句。我尝试的最新查询如下:
SELECT * FROM `payments`
LEFT OUTER JOIN `sales_invoices`
ON `payments`.`account_id` = `sales_invoices`.`account_id`
ORDER BY `created_at` ASC;
The first problem would be that the "created_at" column is ambiguous, so I am not sure how to merge these. The second problem is that it merges rows and brings back duplicate rows which is incorrect.
第一个问题是“created_at”列是不明确的，所以我不确定如何合并这些。第二个问题是，它合并行并返回不正确的重复行。
Am I thinking about this the wrong way or is there a feature of MySQL I am not aware of that can do this for me? Thanks.
是我想错了，还是有我不知道的MySQL特性可以帮我实现?谢谢。
1 个解决方案
#1
2
You can use union all for this. You just need to define the columns carefully:
你可以使用union all。你只需要仔细定义列:
select ip.*
from ((select p.invoice_id, p.amount as payment, NULL as invoice, p.created_at
from payments p
) union all
(select i.invoice_id, NULL, i.amount, i.created_at
from sales_invoices i
)
) ip
order by created_at asc;
Your question is specifically about MySQL. Other databases support a type of join called the full outer join, which can also be used for this type of query (MySQL does not support full outer join).
你的问题是关于MySQL的。其他数据库支持一种称为完整外部连接的连接类型，它也可以用于这种类型的查询(MySQL不支持完全外部连接)。
#1
2
You can use union all for this. You just need to define the columns carefully:
你可以使用union all。你只需要仔细定义列:
select ip.*
from ((select p.invoice_id, p.amount as payment, NULL as invoice, p.created_at
from payments p
) union all
(select i.invoice_id, NULL, i.amount, i.created_at
from sales_invoices i
)
) ip
order by created_at asc;
Your question is specifically about MySQL. Other databases support a type of join called the full outer join, which can also be used for this type of query (MySQL does not support full outer join).
你的问题是关于MySQL的。其他数据库支持一种称为完整外部连接的连接类型，它也可以用于这种类型的查询(MySQL不支持完全外部连接)。