主页 / dba / 问题 / 228914
Accepted
kn3l
Asked: 2019-02-05 23:03:57 +0800 CST

查询条件从值列表中获取的位置

  • 772
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT
       (
         SELECT ForeignCurrencyBal
         FROM [InsightWarehouse].[dbo].[v_Account] AS subA
         WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]
               AND 
               subA.AccountNum = STMT.ACCOUNT_NUMBER
       ) AS Balance,
       ACCOUNT_NUMBER,
       CASE WHEN STMT.CRF_TYPE='CREDIT' 
            THEN STMT.AMOUNT_LCY
            ELSE NULL
       END as 'CreditAmount',
       CASE WHEN STMT.CRF_TYPE='DEBIT' 
            THEN STMT.AMOUNT_LCY
            ELSE  NULL
       END as 'DebitAmount',
       STMT.BOOKING_DATE,
       STMT.VALUE_DATE,
       CRF_TYPE
FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
     LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A 
     ON a.AccountNum = STMT.ACCOUNT_NUMBER
WHERE STMT.MIS_DATE='2019-02-01' 
      AND 
      STMT.ACCOUNT_NUMBER='14498478'

在这一行 WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]，这个列表可以通过子查询得到。

我想把条件放在前一天，这意味着此列表中的第二项。像这样的

BusinessDate='2019-01-31'

sql-server t-sql

3 个回答

  1. Best Answer

    是这样的：

    SELECT 
    DISTINCT (
        SELECT ForeignCurrencyBal
        FROM [InsightWarehouse].[dbo].[v_Account] AS subA
            JOIN (
                SELECT TOP (1) dt
                FROM (
                    VALUES
                        ('2019-02-01')
                    ,   ('2019-01-31')
                    --,etc..
                )d(dt)
                WHERE d.dt < STMT.MIS_DATE
                ORDER BY d.dt DESC
            ) x ON x.dt = BusinessDate
        WHERE subA.AccountNum = STMT.ACCOUNT_NUMBER
    ) AS Balance,

    ACCOUNT_NUMBER,
    CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
        ELSE NULL
    END as 'CreditAmount',
    CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
        ELSE  NULL
    END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,

    CRF_TYPE

FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
    LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER

WHERE STMT.MIS_DATE='2019-02-01'
    AND STMT.ACCOUNT_NUMBER='14498478'

    UPD：请注意您的子查询必须返回一行，所以也许您应该使用TOP (1)或一些聚合函数来避免可能的错误。

    • 1 
  2. declare @Date date='2019-02-01'
declare @Accountno varchar(20)='14498478'

SELECT 
    DISTINCT
    (SELECT ForeignCurrencyBal
          FROM [InsightWarehouse].[dbo].[v_Account] AS subA
          WHERE BusinessDate=dateadd(day,-1,STMT.MIS_DATE)
          AND subA.AccountNum = STMT.ACCOUNT_NUMBER) AS Balance,

    ACCOUNT_NUMBER
        ,CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
          ELSE NULL
          END as 'CreditAmount'
          ,CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
          ELSE  NULL
          END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,
    CRF_TYPE
  FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 

  LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER
  WHERE STMT.MIS_DATE=@Date AND STMT.ACCOUNT_NUMBER=@Accountno

    或者

    declare @Date date='2019-02-01'
declare @Accountno varchar(20)='14498478'

SELECT  
   ForeignCurrencyBal AS Balance,
    ACCOUNT_NUMBER
        ,CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
          ELSE NULL
          END as 'CreditAmount'
          ,CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
          ELSE  NULL
          END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,

    CRF_TYPE
  FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
outer apply(select top 1 ForeignCurrencyBal from [InsightWarehouse].[dbo].[v_Account] AS subA 
where a.AccountNum = STMT.ACCOUNT_NUMBER and BusinessDate<STMT.MIS_DATE order by BusinessDate desc)OA
WHERE STMT.MIS_DATE=@Date AND STMT.ACCOUNT_NUMBER=@Accountno
    • 1

  3. 你可以试试WHERE BusinessDate = CAST(GETDATE() -1 AS date)，这将需要前一天。

    例如2019-01-31it will take2019-01-30和2019-03-01it will take2019-02-28等等。

    此外，如果您只想包含工作日（周一至周五），请添加另一个条件AND DATEPART(dw, GETDATE()-1) NOT IN (7,1)

    • 0

相关问题