問題描述

下午所有

奇怪的是這個.我寫了一個相當簡單的查詢，我認為它工作正常.但是，當我一次又一次地多次運行查詢時，每次都會得到不同數量的行.它是一個實時數據庫，但除了我之外，今天沒有人使用它.我在參數中設置的日期范圍是過去的，所以沒有新的\刪除的記錄會影響它.所以我有點困惑，為什么有時我會得到更多的行，有時我應該每次都返回相同的行，但有時會得到更少的行.復制下面的代碼

Strange one this. I have written a query which is fairly simple and I had assumed it was working ok. However, when I run the query, one after another, multiple times, I get a different number of rows every time. It is a live database, but apart from me, nobody is using it today. The date range I have set in my parameters is way in the past, so no new\deleted records should affect it. So I'm a little confused as to why sometimes I get more rows, sometimes less rows when I should get back the same rows every time. Copy of the code below

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@

推薦答案

以下是 ROW_NUMBER() 查詢如何影響結果數量的簡單示例:

Here's a simple example of how the ROW_NUMBER() query can affect the number of results:

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 子句不足以知道哪些行將被分配哪些行號.運行這個查詢，我通常得到:

You'll notice that my ORDER BY clause is insufficient to know which rows will be assigned which row numbers. Running this query, I usually get:

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

但是，這個結果集也是可能的:

However, this result set is also possible:

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

這取決于哪一行被分配了行號 1.

And that just depends on which row gets assigned row number 1.

如果您需要確定性，請確保在 PARTITION 和 ORDER BY 子句中指定足夠的表達式，以便知道每一行的分配的行號將唯一確定.

If you want determinism, make sure that you specify enough expressions in your PARTITION and ORDER BY clauses so that you know that each row's assigned row number will be uniquely determined.

這篇關于T-SQL 查詢在彼此之后立即運行時返回不同的行數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！