SQL Server/DB2:相同的查询返回不同的结果吗? [英] SQL Server/DB2: Same query returns different results?

问题描述

我目前正在一个项目中，我必须向底层数据库引擎查询代表用户访问的记录的最后更改.

I'm currently working on a project where I have to query against the underlying database engine for last changes of records that represents users accesses.

每个用户都可以(但不是必须)拥有子帐户.子帐户存储在同一数据表中，并通过 ID_PUSR 表字段对其上级进行引用.如果帐户是主帐户，则 ID_PUSR为空

Each user may, and is not mandatory to, have children accounts. Children accounts are stored within the same data table with a reference to its parent through the ID_PUSR table field. When the account is a primary, ID_PUSR is null

每次更改访问权限时，都会在数据库用户表中创建一个新记录，并带有最后更新日期( DT_UPDT ).

Each time an acces is changed, a new record is then created in the database users table, with a last update date (DT_UPDT).

请考虑以下内容:

create table USERS (
   ID_USERS INT // Primary key
    , LN_USER VARCHAR(128)
    , FN_USER VARCHAR(128)
    , CD_USER VARCHAR(128)
    , DT_UPDT DATETIME
    , ID_PUSR INT // Foreign key to USERS.ID_USERS.
)

ID_USERS CD_USER    LN_USER      FN_USER     DT_UPDT                     ID_PUSR
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
   808   T_PEI00    LN_USER_00   FN_USER_00  2011-01-01-00.00.00.000000    NULL
   809   T_PEI00    LN_USER_00   FN_USER_00  2010-01-01-00.00.00.000000    NULL
   810   T_PEI00    LN_USER_00   FN_USER_00  2009-01-01-00.00.00.000000    NULL
   811   T_PEI00    LN_USER_00   FN_USER_00  2008-01-01-00.00.00.000000    NULL
   812   T_PEI00    LN_USER_00   FN_USER_00  2007-01-01-00.00.00.000000    NULL 
   813   T_PEI00A   LN_USER_00   FN_USER_00  2011-01-01-00.00.00.000000    808
   814   T_PEI00A   LN_USER_00   FN_USER_00  2010-01-01-00.00.00.000000    809
   815   T_PEI00A   LN_USER_00   FN_USER_00  2009-01-01-00.00.00.000000    810
   816   T_PEI00A   LN_USER_00   FN_USER_00  2008-01-01-00.00.00.000000    811
   817   T_PEI00A   LN_USER_00   FN_USER_00  2007-01-01-00.00.00.000000    812
   818   T_MAW00    LN_USER_01   FN_USER_01  2010-01-01-00.00.00.000000    NULL 
   819   T_MAW00    LN_USER_01   FN_USER_01  2009-01-01-00.00.00.000000    NULL
   820   T_MAW00    LN_USER_01   FN_USER_01  2008-01-01-00.00.00.000000    NULL
   821   T_MAW00    LN_USER_01   FN_USER_01  2007-01-01-00.00.00.000000    NULL
   822   T_VEM08    LN_USER_08   FN_USER_08  2009-01-01-00.00.00.000000    NULL
   823   T_VEM08    LN_USER_08   FN_USER_08  2008-01-01-00.00.00.000000    NULL
   824   T_VEM08    LN_USER_08   FN_USER_08  2007-01-01-00.00.00.000000    NULL
   825   T_LAC99    LN_USER_99   FN_USER_99  2008-01-01-00.00.00.000000    NULL
   826   T_LAC99    LN_USER_99   FN_USER_99  2007-01-01-00.00.00.000000    NULL

我仔细检查了两个数据库服务器中的数据表内容，可以证明它们是相同的记录.

I double-checked the data table content within both database servers and I can certify that they are identical records.

此查询与SQL Server和DB2数据库引擎完全兼容:

This query is fully compatible with both SQL Server and DB2 database engines:

with UPG as (
    select ID_USERS
            , CD_USER
            , LN_USER
            , FN_USER
            , DT_UPDT
            , ID_PUSR
            , row_number() over (partition by CD_USER order by CD_USER desc) as ROWNUM
        from USERS    
) select ID_USERS
        , CD_USER
        , LN_USER
        , FN_USER
        , DT_UPDT
        , ID_PUSR
        , ROWNUM
    from UPG
    where ROWNUM = 1
    order by CD_USER

---

不同的结果！

尽管我对上述RDBMS运行相同的完全相同的查询，但我得到的结果如下:

---

Different Results!

Despite the fact that I'm running the same exact identical query against the above-mentioned RDBMS, I obtain different results as follows:

ID_USERS CD_USER    LN_USER      FN_USER     DT_UPDT                    ID_PUSR  ROWNUM
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  826    T_LAC99    LN_USER_99   FN_USER_99  2007-01-01-00.00.00.000000  NULL      1
  821    T_MAW00    LN_USER_01   FN_USER_01  2007-01-01-00.00.00.000000  NULL      1
  808    T_PEI00    LN_USER_00   FN_USER_00  2011-01-01-00.00.00.000000  NULL      1
  814    T_PEI00A   LN_USER_00   FN_USER_00  2010-01-01-00.00.00.000000  809       1
  822    T_VEM08    LN_USER_08   FN_USER_08  2009-01-01-00.00.00.000000  NULL      1

在我们看到两个数据库引擎之间的差异之前，这些结果似乎是不错的.请注意 DT_UPDT 字段中的日期值.

These results appears to be good until we can see the difference between the two database engines. Notice the date values in the DT_UPDT field.

ID_USERS CD_USER    LN_USER      FN_USER     DT_UPDT                    ID_PUSR  ROWNUM
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  727    T_LAC99    LN_USER_99   FN_USER_99  2008-01-01 00:00:00.000     NULL      1
  720    T_MAW00    LN_USER_01   FN_USER_01  2010-01-01 00:00:00.000     NULL      1
  710    T_PEI00    LN_USER_00   FN_USER_00  2011-01-01 00:00:00.000     NULL      1
  715    T_PEI00A   LN_USER_00   FN_USER_00  2011-01-01 00:00:00.000     710       1
  724    T_VEM08    LN_USER_08   FN_USER_08  2009-01-01 00:00:00.000     NULL      1

在SQL Server中，这些结果是我将在DB2中获得的结果.它们代表良好"数据.至于 ID_USERS ，它们只是ID，重要的是日期.

These results here in SQL Server are the one with which I shall come in DB2. They represent the "good" datum. As for the ID_USERS, they are just ID's, what matters are the dates.

1. 同一查询如何使用两个支持SQL ANSI的引擎返回不同的结果?
2. 是否有我似乎看不见的基准?
3. DB2如何解释与SQL Server不同的 WITH ... AS()?

Nota Benne:一个简单的 select *从CD_USER的USERS订单中显示相同的数据.

Nota Benne: A simple select * from USERS order by CD_USER reveals the same data.

推荐答案

您的查询对联系没有确定性.

Your query is undeterministic for ties.

row_number()以上(按CD_USER命令的CD_USER顺序分区)为ROWNUM

没有在每个分区内定义任何特定的行号.如果您希望两个RDBMS都按唯一的方式返回相同的结果顺序，那么就没有联系和确定性的结果.

doesn't define any particular row_numbering within each partition. If you want both RDBMS's to return the same results order by something unique so there are no ties and deterministic results.

这篇关于SQL Server/DB2:相同的查询返回不同的结果吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！