主页 / dba / 问题 / 224638
Accepted
kn3l
Asked: 2018-12-12 02:02:22 +0800 CST

从子查询中选择字段，但我面对的字段无法绑定

  • 772
SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM         
     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE     
    a.AccountNum IN ( 
        SELECT  aa.AccountNum FROM v_Account AS aa WHERE   
         aa.Category = 'Deposit' AND aa.ProductCode IN ('6003', '6253') 
        AND aa.Businessdate='2018-12-06' AND aa.ClosedDate is not NULL
    )  
    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'

我如何aa.ClosedDate从我的子查询或任何替代解决方案中选择我可以获得的 ClosedDate 2018-12-06。

例子。帐号：123456 在 2018-12-06 关闭（关闭意味着没有余额）所以为了获得这个帐号的余额，我需要在 2018-12-05 查找这个（在 1 天之前这个帐户被关闭) ，然后我可以获得余额以用于我的计算目的。

sql-server

2 个回答

  1. Best Answer

    根据您对给定帐号的子查询中是否可以出现多行的问题的评论，您表示这是不可能的。如果那是真的，我不明白为什么您不能简单地LEFT JOIN根据帐号对子查询的派生结果进行查询。

    没有测试过，但基本上是这样的：

    SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM         
     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate
LEFT JOIN
(
    SELECT AccountNum, ClosedDate FROM v_Account WHERE   
        Category = 'Deposit' AND ProductCode IN ('6003', '6253') 
    AND Businessdate='2018-12-06' AND ClosedDate is not NULL
) aa on aa.AccountNum = a.AccountNum
WHERE 
        aa.AccountNum = a.AccountNum
    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'
    • 1 
  2. SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    c.ClosedDate,
    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'
FROM         

     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE     

    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND (a.Businessdate='2018-12-05' OR a.Businessdate='2018-12-06' AND 
         a.ClosedDate is not NULL)
    • 0

相关问题