mysql子查询引用外表

Question

我正在尝试在子查询选择中使用外表。虽然我可以对值进行硬编码并获得所需的结果；我确实需要使用外部 phppos_items 表；但无法弄清楚。以下是简化的查询。有什么办法可以做到这一点吗？我使用子查询的原因是我只想在连接中获取一行。

我试图获取外表的部分是XXX

SELECT SUM(trans_current_quantity) as quantity FROM `phppos_items` 
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and `phppos_location_items`.`location_id` IN(1)
LEFT JOIN 
(SELECT * FROM phppos_inventory WHERE trans_date < '2018-05-06 23:59:59' and
trans_items = *XXX* ORDER BY trans_date DESC LIMIT 1) as inventory 
ON `phppos_location_items`.`item_id` = `inventory`.`trans_items`

Answer 1

这是您的查询，表别名使其更具可读性:

SELECT SUM(trans_current_quantity) as quantity
FROM phppos_items i LEFT JOIN
     phppos_location_items li
     ON li.item_id = i.item_id and li.location_id IN (1) LEFT JOIN 
     (SELECT * 
      FROM phppos_inventory inv
      WHERE inv.trans_date < '2018-05-06 23:59:59' and
            trans_items = *XXX*
      ORDER BY trans_date DESC LIMIT 1
     ) inv 
    ON li.item_id = inv.trans_items;

我只能将 trans_current_quantity 解释为来自 phppos_inventory。当您编写查询时，LEFT JOIN 似乎是多余的。

你真正想要的是横向连接。唉，这在 MySQL 中不起作用。这是下一个最接近的东西:

SELECT SUM(inv.trans_current_quantity) as quantity
FROM phppos_items i JOIN
     phppos_location_items li
     ON li.item_id = i.item_id and li.location_id IN (1) JOIN 
     phppos_inventory inv
     ON li.item_id = inv.trans_items
WHERE li.item_id = XXX AND
      inv.trans_date = (SELECT MAX(inv2.trans_date)
                        FROM phppos_inventory inv2
                        WHERE inv2.trans_date < '2018-05-07' and
                              inv2.trans_items = li.item_id
                       );

我还通过添加第二个来修复日期比较。如果不正确，您可以修复它，但查询更具可读性。