时间戳

首页 > 解决方案 > 如何使用多列作为别名进行子查询

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

问题描述

我有两个 SQL 表，“Debits”和“Debits_Line_Items”。我想运行“借记”表的 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
          }         
       }
    ]

标签： mysqlsqljoinleft-join

解决方案

尝试这样的事情：

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 链接。然后我们可以使用一些示例数据为您验证代码。

推荐阅读