代码之家  ›  专栏  ›  技术社区  ›  Malfist

将子查询结果用作查询变量

  •  3
  • Malfist  · 技术社区  · 15 年前

    我正在尝试将我的一个子查询的结果用于另一个子查询,但它不起作用。

    这是一个查询:

    SELECT t.TenantName, 
        (SELECT SUM(Amount) 
         FROM tblTransaction 
         WHERE Amount > 0 
            AND TransactionDate >= '12/01/09' 
            AND TransactionDate <= '12/31/09'
            AND TenantID = t.ID 
            AND TransactionCode = trans.TransactionCode) AmountPaid, 
        (SELECT SUM(Amount) 
         FROM tblTransaction 
         WHERE tblTransaction.TransactionCode = trans.TransactionCode 
            AND tblTransaction.TenantID = t.ID) - AmountPaid AmountOwedTotal, 
        (SELECT SUM(Amount) 
         FROM tblTransaction 
         WHERE  tblTransaction.TransactionCode = trans.TransactionCode 
            AND tblTransaction.TenantID = t.ID
           AND Amount < 0 AND TransactionDate >= '12/01/09' 
            AND TransactionDate <= '12/31/09') AmountOwedThisMonth, 
       code.Description, trans.TransactionDate 
    FROM tblTransaction trans 
       LEFT JOIN tblTenantTransCode code 
          ON code.ID = trans.TransactionCode
       LEFT JOIN tblTenant t 
          ON t.ID = trans.TenantID
    WHERE trans.TransactionDate >= '12/01/09' 
       AND trans.TransactionDate <= '12/31/09' 
       AND trans.Amount > 0
    

    对不起,事情太复杂了。

    我在哪里 子查询 )-amountpaid SQLServer抱怨amountpaid不是有效的列名。如何访问它的子查询结果?

    2 回复  |  直到 15 年前
        1
  •  4
  •   Quassnoi    15 年前
    SELECT  TenantName,
            Description,
            TransactionDate,
            AmountPaid,
            AmountRequired - AmountPaid AS AmountOwedTotal,
            AmountOwedThisMonth
    FROM    (
            SELECT  t.TenantName,
                    code.Description,
                    trans.TransactionDate,
                    (
                    SELECT  SUM(Amount)
                    FROM    tblTransaction
                    WHERE   Amount > 0
                            AND    TransactionDate >= '12/01/09'
                            AND    TransactionDate <= '12/31/09'
                            AND    TenantID = t.ID
                            AND    TransactionCode = trans.TransactionCode
                    ) AS AmountPaid,
                    (
                    SELECT  SUM(Amount)
                    FROM    tblTransaction
                    WHERE   tblTransaction.TransactionCode = trans.TransactionCode
                            AND    tblTransaction.TenantID = t.ID
                    ) AS AmountRequired,
                    (
                    SELECT SUM(Amount)
                    FROM   tblTransaction
                    WHERE  tblTransaction.TransactionCode = trans.TransactionCode
                            AND    tblTransaction.TenantID = t.ID
                            AND    Amount < 0
                    AND    TransactionDate >= '12/01/09'
                    AND    TransactionDate <= '12/31/09'
                    ) AS AmountOwedThisMonth,
            FROM    tblTransaction trans
            LEFT JOIN
                    tblTenantTransCode code
            ON      code.ID = trans.TransactionCode
            LEFT JOIN       
                    tblTenant t
            ON      t.ID = trans.TenantID
            WHERE   trans.TransactionDate >= '12/01/09'
                    AND trans.TransactionDate <= '12/31/09'
                    AND trans.Amount > 0
            ) q
    
        2
  •  0
  •   Charles Bretana    15 年前

    试试这个:

       Select t.TenantName, c.Description, tx.TransactionDate, 
         Sum(Case When TransactionDate Between '12/01/09' AND '12/31/09'
                       And Amount > 0
                  Then tx.Amount Else 0 End) AmountPaid,
         Sum(tx.Amount) Total,
         Sum(tx.Amount) -  
         Sum(Case When TransactionDate Between '12/01/09' AND '12/31/09'
                       And Amount > 0
                  Then tx.Amount Else 0 End) AnountOwed,
         Sum(Case When TransactionDate Between '12/01/09' AND '12/31/09'
                       And Amount < 0
                  Then tx.Amount Else 0 End) AnountOwedThisMonth          
        FROM tblTransaction tx    
           LEFT JOIN tblTenantTransCode c 
              ON c.ID = tx.TransactionCode
           LEFT JOIN tblTenant t
               ON t.ID = tx.TenantID
        Group By t.TenantName, c.Description