Oracle中rownum与distinct的使用陷阱解析

"Oracle数据库中的rownum和distinct是两个重要的关键字，分别用于行编号和去除重复数据。rownum是在查询过程中动态赋予每一行的序列号，而distinct则在查询结束后删除重复记录。当这两个关键字结合使用时，可能会出现不预期的结果。" 在Oracle数据库中，rownum是一个伪列，它为查询结果集中的每一行赋予一个唯一的整数。这个编号从1开始，并随着每一行的返回而递增。rownum的特性决定了它可以在查询语句中用于限制返回的行数，例如，通过`rownum < 3`来获取前两行数据。然而，值得注意的是，rownum的赋值是基于查询结果的返回顺序，而不是表中的物理顺序。这意味着如果查询中有order by子句，rownum的值将基于排序后的顺序。 另一方面，distinct关键字用于消除查询结果中的重复行。它在所有记录检索出来之后，对整个结果集进行处理，删除重复的行。由于这个过程发生在所有数据都已获取之后，所以distinct无法与rownum配合实时过滤重复数据。 现在让我们看一个具体的例子来理解rownum和distinct结合使用时的问题。假设有一个查询： ```sql SELECT DISTINCT a.access_id, a.link_id FROM asn_access a, asn_res_link b WHERE a.access_id = '000000000000000000035696' AND a.link_id = b.link_id AND a.delete_state = '0' AND b.delete_state = '0'; ``` 这个查询会返回去重后的access_id和link_id组合。如果在上面的查询基础上添加rownum限制，如`rownum < 3`： ```sql SELECT DISTINCT a.access_id, a.link_id FROM asn_access a, asn_res_link b WHERE a.access_id = '000000000000000000035696' AND a.link_id = b.link_id AND a.delete_state = '0' AND b.delete_state = '0' AND rownum < 3; ``` 这里可能出现的误解是，期望得到两条记录，因为distinct已经去重，rownum限制了返回的行数。然而，实际情况是，rownum在distinct之前应用，也就是说，它会先对原始未去重的数据进行限制。因此，如果去重后的数据中第一条和第二条是相同的，那么rownum<3只会返回一条记录，而不是两条。 为了在有限制的情况下获取预期的去重结果，可以使用子查询或分析函数，如row_number() over (partition by ... order by ...)，这将先进行去重操作，然后再应用行限制。这样可以确保rownum基于去重后的行进行计数。 理解rownum和distinct在查询中的工作方式至关重要，以避免在实际使用时产生错误的预期。正确使用这两个关键字的组合，可以帮助我们更有效地从大型数据集中提取所需的信息。