时间戳

首页 > 解决方案 > 如何从oracle sql中的选择计数中对rownum使用case语句？

sql - 如何从oracle sql中的选择计数中对rownum使用case语句？

问题描述

我对 SQL 有点陌生，并且有一个关于 where 子句中基于 count(*) 的 case 语句的问题。

我有一个名为 CARS 的表，我正在查询特定的制造商和汽车的制造日期。

从那里，假设我正在随机检查一些汽车以确保数据正确，所以我想首先随机排序结果，然后根据我返回的结果数量选择“x”行（查询的结果越多，我随机检查的越多）

在下面的查询中，我首先从 CARS 表中获取特定的汽车，获取结果的行数，然后我想做一个案例语句：如果结果，这里是前 'y' 行。

select count(*) over() as carcount,
    vehicles.*
from (
        select *
        from CARS
        where MANUFACTURER IN (
                'BMW',
                'Ford',
                'Volkswagen',
                'Toyota',
                'Saab',
                'Porsche',
                'Hyundai',
                'Alfa Romeo'
            )
            and MANUFACTURED_DATE >= sysdate
            and MANUFACTURED_DATE <= (SYSDATE + 30)
            and MANUFACTURED_DATE is not null
        ORDER BY DBMS_RANDOM.RANDOM
    ) vehicles
where rownum < (
        CASE
            WHEN carcount = 1 THEN 1
            WHEN carcount = 2 THEN 2
            WHEN carcount >= 3 AND carcount <= 4 THEN 2
            WHEN carcount >= 5 AND carcount <= 14 THEN 4
            WHEN carcount >= 15 AND carcount <= 52 THEN 15
            WHEN carcount >= 53 AND carcount <= 365 THEN 25
            WHEN carcount > 365 THEN 30
            ELSE 0
        END
    );

当我运行我的查询时，我收到了错误：

ORA-00904: "carcount": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

我不太确定为什么 carcount 不是有效标识符，以及如何在随机结果中获得前“x”行。我将不胜感激任何帮助/建议 - 在此先感谢您！

标签： sqloraclecasewhere-clausewindow-functions

解决方案

carcount应该在子查询中计算才能使查询正常工作。我会更进一步并使用row_number()而不是rownum：

select *
from (
    select 
        c.*, 
        row_number() over(order by dbms_random.random) rn,
        count(*) over() cnt
    from cars c
    where 
        manufacturer in (
            'bmw',
            'ford',
            'volkswagen',
            'toyota',
            'saab',
            'porsche',
            'hyundai',
            'alfa romeo'
        )
        and manufactured_date >= sysdate
        and manufactured_date <= sysdate + 30
) c
where rn <= case
    when cnt >  365 then 30
    when cnt >= 53  then 4
    when cnt >= 15  then 15
    when cnt >= 5   then 4
    when cnt >= 2   then 2
    else 1
end

旁注：

  • 子查询中的条件and manufactured_date is not null是多余的 - 您已经在此列上有不等式谓词，它会过滤掉null值

  • 表达式可以通过首先排序具有高值的case条件来简化 - 还有一些冗余条件，第二个分支中的目标行数似乎不公平地低

  • 似乎您想要<=而不是<行号的不等式条件（否则，例如，当子查询仅返回 1 行时，您的查询将过滤掉结果）

推荐阅读