sql - 为什么使用 "where rownum = 1"不选择第一个有序行？

Question

这很奇怪，所以我可以用一双备用的眼睛来了解发生了什么。

所以我有这个查询:

WITH x as (
            SELECT num_aula, tipo_aula, min(abs(capienza-1)) score
            FROM aula
            JOIN (
                    select num_aula, tipo_aula
                    from aula
                    where tipo_aula = 'Laboratorio'
                    minus
                    select num_aula, tipo_aula
                    from occr_lezione
                    where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
                    and Nome_sede = 'Centro Direzionale'
                    and Giorno_lezione = 2
                 )
            USING(num_aula,tipo_aula)
            GROUP BY num_aula, tipo_aula
            ORDER BY score asc
)
SELECT *
FROM x

返回此结果集:

NUM TIPO_AULA                 SCORE
--- -------------------- ----------
1   Laboratorio                  35
2   Laboratorio                  35

这是期望的结果。

现在，如果我将这一行添加到查询中:

WHERE rownum = 1;

它应该返回表格的第一行，我明白了:

NUM TIPO_AULA                 SCORE
--- -------------------- ----------
2   Laboratorio                  35

这怎么可能？

Answer 1

我认为你真正想要的查询是

WITH x as (
    SELECT num_aula, 
           tipo_aula, min(abs(capienza-1)) score, 
           row_number() over(partition by num_aula, tipo_aula order by score asc ) as seq
    FROM aula
    JOIN (
        select num_aula, tipo_aula
          from aula
          where tipo_aula = 'Laboratorio'
          minus
          select num_aula, tipo_aula
            from occr_lezione
            where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
            and Nome_sede = 'Centro Direzionale'
            and Giorno_lezione = 2
        )
        USING(num_aula,tipo_aula)
)
SELECT *
FROM x
WHERE x.seq = 1;

ROWNUM 关键字的行为与您想象的不同，请参阅 this article about rownum .

为了提供更多详细信息，ROWNUM 在对结果集给出任何顺序之前分配。

如果您真的想使用 ROWNUM 关键字获得正确的结果，那么您可以使用首先排序的子查询来实现这一点，然后为实际排序的结果集生成 rownum。但是，我更喜欢第一种方法，因为我认为它更具可读性，但您可以自由选择这种方法。

SELECT *
FROM (SELECT num_aula, 
           tipo_aula, min(abs(capienza-1)) score
        FROM aula
        JOIN (
            select num_aula, tipo_aula
            from aula
            where tipo_aula = 'Laboratorio'
            minus
                select num_aula, tipo_aula
                from occr_lezione
                where to_char(Data_inizio_occr_lezione,'hh24:mi') = '12:30'
                  and Nome_sede = 'Centro Direzionale'
                  and Giorno_lezione = 2
            ) USING(num_aula,tipo_aula)
        GROUP BY num_aula, tipo_aula
        ORDER BY score asc) x
WHERE x.rownum = 1;