mysql - 如何以多列作为别名进行子查询

Question

我有两个 SQL 表:“Debits”和“Debits_Line_Items”。我想运行“Debits”表的 SQL 查询，其中每个借方记录都包含“Debits_Line_Items”表中相应记录的列表，这些记录应与 CartID 值匹配。

这是我尝试过的方法，它产生了“lineItems”列未知的错误:

SELECT 
    CartID AS cartId,
    TotalCost AS totalCost,
    lineItems
FROM Debits
LEFT JOIN 
    (
        SELECT 
            Price AS cost,
            Quantity AS quantity,
            TotalPrice as total 
        FROM Debits_Line_Items
        WHERE UserID = 9902
    ) AS lineItems
ON Debits.CartID = lineItems.CartID
WHERE UserID = 9902

这就是我所希望的:

   [
      {
        cartId: 3225,
        totalCost: 212,
        lineItems: [
          {
             cost: 32,
             quantity: 1, 
             total: 32
          }, 
          {
             cost: 60,
             quantity: 3, 
             total: 180
          },          
       },
       {
        cartId: 3226,
        totalCost: 75,
        lineItems: [
          {
             cost: 15,
             quantity: 5, 
             total: 75
          }         
       }
    ]

Answer 1

尝试这样的事情:

WITH lineItems(cost, quantity, total, CartID) AS (
     SELECT 
           Price AS cost,
           Quantity AS quantity,
           TotalPrice as total 
      FROM 
            Debits_Line_Items
      WHERE 
            UserID = 9902
)
SELECT 
      d.CartID AS cartId,
      d.TotalCost AS totalCost,
      lineItems.*
FROM  
      Debits d
LEFT JOIN lineItems l
  ON d.CartID = l.CartID
WHERE 
      d.UserID = 9902

如果有帮助请告诉我。始终最好与 reextester 链接。然后我们可以使用一些示例数据为您验证代码。