sql - T-SQL 查询在彼此紧接着运行时返回不同行数

Question

下午好

这很奇怪。我写了一个相当简单的查询，我以为它工作正常。但是，当我多次运行查询时，每次都会得到不同数量的行。它是一个实时数据库，但除了我之外，今天没有人在使用它。我在参数中设置的日期范围是过去的方式，因此没有新的\删除的记录会影响它。所以我有点困惑，为什么有时我会得到更多的行，有时却得到更少的行，而我每次都应该得到相同的行。以下代码的副本

DECLARE 
        @From date,
        @To date

SET @From = '01/07/2015'
SET @To = '31/12/2015'

--AS


SELECT DISTINCT
    BR.Branch, 
    DIV.Division,
    BCM.ClientRef@ AS 'Client Reference',
    BPY.PolicyRef@ AS 'Policy Reference',
    AE.Name AS 'Account Executive',
    DIV2.#Name AS 'Account Handler',
    BTX.Dt_raised AS 'Effective Date',
    BTX.Ledger_dt AS 'Ledger Date',
    INS.VTDescription AS 'Insurer',
    BPY.Ptype AS 'Policy Type',
    BTX.Orig_Debt AS 'GWP',
    COALESCE(BTX.Comm_amt, 0) AS 'Commission',
    CASE
        WHEN BTX.Ccode = '7' THEN 0
        ELSE COALESCE(CHG.Orig_Debt, 0)
    END AS 'Fee',
    COALESCE(BTX.Comm_amt, 0) + CASE WHEN BTX.Ccode = '7' THEN 0 ELSE COALESCE(CHG.Orig_Debt, 0) END AS 'Income',
    CASE DATENAME(MONTH, BTX.Dt_Raised)
        WHEN 'July' THEN YEAR(BTX.Dt_Raised)
        WHEN 'August' THEN YEAR(BTX.Dt_Raised)
        WHEN 'September' THEN YEAR(BTX.Dt_Raised)
        WHEN 'October' THEN YEAR(BTX.Dt_Raised)
        WHEN 'November' THEN YEAR(BTX.Dt_Raised)
        WHEN 'December' THEN YEAR(BTX.Dt_Raised)
        WHEN 'January' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
        WHEN 'February' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
        WHEN 'March' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
        WHEN 'April' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
        WHEN 'May' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
        WHEN 'June' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
    END AS 'FinYear',
    CASE DATENAME(MONTH, BTX.Dt_Raised)
        WHEN 'January' THEN 'Ja'
        WHEN 'February' THEN 'Fe'
        WHEN 'March' THEN 'Ma'
        WHEN 'April' THEN 'Ap'
        WHEN 'May' THEN 'My'
        WHEN 'June' THEN 'Ju'
        WHEN 'July' THEN 'Jy'
        WHEN 'August' THEN 'Au'
        WHEN 'September' THEN 'Se'
        WHEN 'October' THEN 'Oc'
        WHEN 'November' THEN 'No'
        WHEN 'December' THEN 'De'
    END AS 'Month Letter',
    DATENAME(MONTH, BTX.Dt_Raised) AS 'MonthName',
    CASE DATENAME(MONTH, BTX.Dt_Raised)
        WHEN 'January' THEN 7
        WHEN 'February' THEN 8
        WHEN 'March' THEN 9
        WHEN 'April' THEN 10
        WHEN 'May' THEN 11
        WHEN 'June' THEN 12
        WHEN 'July' THEN 1
        WHEN 'August' THEN 2
        WHEN 'September' THEN 3
        WHEN 'October' THEN 4
        WHEN 'November' THEN 5
        WHEN 'December' THEN 6
    END AS 'Order',
    CASE ROW_NUMBER() OVER (PARTITION BY BCM.ClientRef@ ORDER BY BCM.ClientRef@)
        WHEN 1 THEN 1 ELSE 0
    END AS 'Client Count', 
    CASE ROW_NUMBER() OVER (PARTITION BY BPY.PolicyRef@ ORDER BY BPY.PolicyRef@)
        WHEN 1 THEN 1 ELSE 0
    END AS 'Policy Count'
FROM icp_yyclient AS BCM 
INNER JOIN icp_brpolicy AS BPY ON BCM.ClientRef@ = BPY.ClientRef@ AND BCM.Branch@ = BPY.Branch@
INNER JOIN icp_brcledger AS BTX ON BPY.PolicyRef@ = BTX.PolicyRef@ AND BPY.Branch@ = BTX.Branch@
LEFT JOIN icp_brcledger AS CHG ON BTX.Chg_ptr = CHG.Suffix AND BTX.PolicyRef@ = CHG.PolicyRef@ AND BTX.Branch@ = CHG.Branch@
LEFT JOIN AccountExecutives AS AE ON BCM.Job1 = AE.Code
LEFT JOIN icp_bredetail AS DIV2 ON BPY.#Exec = DIV2.Code AND BPY.Branch@ = DIV2.Branch@
LEFT JOIN Division_VT AS DIV ON DIV2.Ext_department = DIV.Division
LEFT JOIN Branch_VT AS BR ON DIV.Branch = BR.VTId
LEFT JOIN icp_INSC_VT AS INS ON BPY.Insco_INSC_VTId = INS.VTId

WHERE
    BPY.Branch@ = 0
    AND BTX.Dt_Raised BETWEEN @From AND @To
    AND (BTX.Trantype IN ('New Business') AND BTX.Trantype <> 'Charge')
    AND (BPY.Term_code IS NULL AND BPY.Term_date IS NULL)
ORDER BY BR.Branch, DIV.Division, BCM.ClientRef@

Answer 1

下面是一个简单示例，说明 ROW_NUMBER() 查询如何影响结果数量:

declare @t table (a int, b int, c int)
insert into @t(a,b,c) values
(1,1,1),
(1,1,1),
(1,1,2)

select distinct a,c,CASE ROW_NUMBER() OVER (ORDER BY b) WHEN 1 THEN 1 ELSE 0 END
from @t

您会注意到我的 ORDER BY 子句不足以知道哪些行将分配给哪些行号。运行这个查询，我通常得到:

a           c           
----------- ----------- -----------
1           1           0
1           1           1
1           2           0

但是，这个结果集也是可能的:

a           c           
----------- ----------- -----------
1           1           0
1           2           1

这仅取决于哪一行被分配行号 1。

如果您想要确定性，请确保您在 PARTITION 和 ORDER BY 子句中指定了足够的表达式，以便您知道每一行的分配的行号将被唯一确定。