• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

T-SQL SELECT TOP返回重复项

sql 来源:Marcus K 7次浏览

我正在使用SQL Server 2008 R2。T-SQL SELECT TOP返回重复项

我不确定我是否发现了一个奇怪的SQL怪癖,或者(更可能)是我的代码中的某些内容导致了这种奇怪的行为,特别是因为Google什么也没有发现。我有一个名为vwResponsible_Office_Address的视图。

SELECT * FROM vwResponsible_Office_Address 

..returns 403行

此代码:

SELECT TOP 1000 * FROM vwResponsible_Office_Address 

..returns 409行,因为它包括6个重复。

然而这样的:

SELECT TOP 1000 * FROM vwResponsible_Office_Address 
ORDER BY ID 

再次..returns 403行。

我可以发布视图的代码,如果它是相关的,但它是否有意义的SELECT TOP以这种方式工作?我知道SELECT TOP可以按任何顺序自由地返回记录,但不明白为什么返回的记录数量应该有所不同。

该视图确实使用了可能影响结果集的交叉应用程序?

编辑:视图定义为要求

CREATE VIEW [dbo].[vwResponsible_Office_Address] 
AS 
    SELECT fp.Entity_ID [Reg_Office_Entity_ID], 
      fp.Entity_Name [Reg_Office_Entity_Name], 
      addr.Address_ID 
    FROM [dbo].[Entity_Relationship] er 
    INNER JOIN [dbo].[Entity] fp 
     ON er.[Related_Entity_ID] = fp.[Entity_ID] 
    INNER JOIN [dbo].[Entity_Address] ea 
     ON ea.[Entity_ID] = fp.[Entity_ID] 
    CROSS APPLY (
     SELECT TOP 1 Address_ID 
     FROM [dbo].[vwEntity_Address] vea 
     WHERE [vea].[Entity_ID] = fp.Entity_ID 
     ORDER by ea.[Address_Type_ID] ASC, ea.[Address_ID] DESC 
    ) addr 
    WHERE [Entity_Relationship_Type_ID] = 25 -- fee payment relationship 

    UNION 

    SELECT ets.[Entity_ID], 
      ets.[Entity_Name], 
      addr.[Address_ID] 
    FROM dbo.[vwEntity_Entitlement_Status] ets 
    INNER JOIN dbo.[Entity_Address] ea 
     ON ea.[Entity_ID] = ets.[Entity_ID] 
    CROSS APPLY (
     SELECT TOP 1 [Address_ID] 
     FROM [dbo].[vwEntity_Address] vea 
     WHERE vea.[Entity_ID] = ets.[Entity_ID] 
     ORDER by ea.[Address_Type_ID] ASC, ea.[Address_ID] DESC 
    ) addr 
    WHERE ets.[Entitlement_Type_ID] = 40 -- registered office 
    AND ets.[Entitlement_Status_ID] = 11 -- active 


===========解决方案如下:

我会假设存在一些非确定性,这意味着不同的访问方法可能返回不同的结果。

看看视图定义,如果vwEntity_AddressEntity_ID有一些重复,那么看起来可能会是唯一的地方。

这会使top 1 Address_ID在这种情况下返回任意值,这将在删除重复项时影响union操作的结果。

无疑这看起来非常可疑

SELECT TOP 1 [Address_ID] 
     FROM [dbo].[vwEntity_Address] vea 
     WHERE vea.[Entity_ID] = ets.[Entity_ID] 
     ORDER by ea.[Address_Type_ID] ASC, ea.[Address_ID] DESC 

您是通过从值在交叉应用外部查询订购。这将完全没有任何影响,因为这些对于特定的CROSS APPLY调用将是不变的。

你可以尝试改变,以

SELECT TOP 1 [Address_ID] 
     FROM [dbo].[vwEntity_Address] vea 
     WHERE vea.[Entity_ID] = ets.[Entity_ID] 
     ORDER by vea.[Address_ID] DESC 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)