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.
- 同一查询如何使用两个支持SQL ANSI的引擎返回不同的结果?
- 是否有我似乎看不见的基准?
- 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屋!