如何在MariaDB 5.5中为单个派生表设置多个别名?

Question

考虑一个包含三个表的数据库:

goods (Id为主键)

+----+-------+-----+
| Id | Name  | SKU |
+----+-------+-----+
|  1 | Nails | 123 |
|  2 | Nuts  | 456 |
|  3 | Bolts | 789 |
+----+-------+-----+

invoiceheader (Id为主键)

+----+--------------+-----------+---------+
| Id |     Date     | Warehouse | BuyerId |
+----+--------------+-----------+---------+
|  1 | '2021-10-15' |         1 |     223 |
|  2 | '2021-09-18' |         1 |     356 |
|  3 | '2021-07-13' |         2 |       1 |
+----+--------------+-----------+---------+

invoiceitems (Id为主键)

+----+----------+--------+-----+-------+
| Id | HeaderId | GoodId | Qty | Price |
+----+----------+--------+-----+-------+
|  1 |        1 |      1 |  15 |   1.1 |
|  2 |        1 |      3 |   7 |   1.5 |
|  3 |        2 |      1 |  12 |   1.5 |
|  4 |        3 |      3 |   3 |   1.3 |
+----+----------+--------+-----+-------+

我要做的是得到每一个invoiceitems.GoodId的MAX(invoiceheader.Date)。或者，在日常生活中，最好是在一个查询中，找出任何货物最后一次从特定仓库出售是什么时候。

要做到这一点，我使用派生查询，这里提出的解决方案。为了能够做到这一点，我认为我需要有一种方法来为派生表提供多个(好吧，两个)别名。

我的查询现在是这样的:

SELECT tmp.*   /* placing the second alias here, before or after tmp.* doesn't work */
FROM (         /* placing the second alias, tmpClone, here also doesn't work */
SELECT
invoiceheader.Id,
invoiceheader.Date,
invoiceitems.HeaderId,
invoiceitems.Id,
invoiceitems.GoodId
FROM invoiceheader
LEFT JOIN invoiceitems
ON invoiceheader.Id = invoiceitems.HeaderId
WHERE invoiceheader.Warehouse = 3
AND invoiceheader.Date > '0000-00-00 00:00:00'
AND invoiceheader.Date IS NOT NULL
AND invoiceheader.Date > ''
AND invoiceitems.GoodId > 0
ORDER BY 
invoiceitems.GoodId ASC,
invoiceheader.Date DESC
) tmp, tmpClone /* this doesn't work with or without a comma */
INNER JOIN (
SELECT
invoiceheader.Id,
MAX(invoiceheader.Date) AS maxDate
FROM tmpClone
WHERE invoiceheader.Warehouse = 3
GROUP BY invoiceitems.GoodId
) headerGroup
ON tmp.Id = headerGroup.Id
AND tmp.Date = headerGroup.maxDate
AND tmp.HeaderId = headerGroup.Id

是否可以为单个派生表设置多个别名?如果是，我该怎么做?

我正在使用5.5.52-MariaDB.

Answer 1

你可以同时使用(inner select)和左连接来实现这一点，例如:选择t1。b(选择t2。b from table2 as t2 where t1.x=t2.x) from table as t1 where t1.x=t2.x)y =(选择t3。where t2.a=t3.a)

Answer 2

虽然这并没有回答我最初的问题，但它确实解决了引起问题的问题，我将把它留在这里，以防有人遇到类似的问题。

下面的查询完成了我想要做的事情——从特定仓库中查找商品的最新销售日期。

SELECT
invoiceheader.Id,
invoiceheader.Date,
invoiceitems.HeaderId,
invoiceitems.Id,
invoiceitems.GoodId
FROM invoiceheader
INNER JOIN invoiceitems
ON invoiceheader.Id = invoiceitems.HeaderId
INNER JOIN (
SELECT
MAX(invoiceheader.Date) AS maxDate,
invoiceitems.GoodId
FROM invoiceheader
INNER JOIN invoiceitems
ON invoiceheader.Id = invoiceitems.HeaderId
WHERE invoiceheader.Warehouse = 3
AND invoiceheader.Date > '0000-00-00 00:00:00'
AND invoiceheader.Date IS NOT NULL
AND invoiceheader.Date > ''
GROUP BY invoiceitems.GoodId
) tmpDate
ON invoiceheader.Date = tmpDate.maxDate
AND invoiceitems.GoodId = tmpDate.GoodId
WHERE invoiceheader.Warehouse = 3
AND invoiceitems.GoodId > 0
ORDER BY 
invoiceitems.GoodId ASC,
invoiceheader.Date DESC

技巧是通过考虑两件事来加入-MAX(invoiceheader.Date)和invoiceitems.GoodId-因为一个GoodId只能在特定的invoiceheader/invoiceitemsJOIN中出现一次(严格限制插入invoiceitems的代码部分)。

这是否是最优的解决方案(忽略查询中的冗余条件)，以及它是否可以很好地扩展，还有待观察-它已经在invoiceheader有~5000个条目，invoiceitems有~60000个条目，goods有~4000个条目的表上进行了测试。执行时间为<1秒。