如何在oraclesql中使用select计数的rownum上的case语句?

pprl5pva  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(288)

我对sql有点陌生,有一个关于where子句中基于count(*)的case语句的问题。
我有一个名为cars的表,我正在查询汽车的具体制造商和生产日期。
从这里开始,假设我随机检查了一些汽车,以确保数据是正确的,所以我想首先随机排序结果,然后根据返回的结果数选择“x”行数(查询的结果越多,我随机检查的越多)
在下面的查询中,我首先从cars表中获取特定的cars,得到结果的行数,然后我想做一个case语句:如果结果中有'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”行。如果有任何帮助/建议,我将不胜感激-提前谢谢!

2vuwiymt

2vuwiymt1#

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 在子查询中是多余的-此列上已经有不等式 predicate ,这会过滤掉 null 价值观
这个 case 表达式可以通过先对具有高值的条件进行排序来简化-还存在一些冗余条件,并且第二个分支中的目标行数似乎不公平地低
好像你想要 <= 而不是 < 在行号的不等式条件中(否则,您的查询将在子查询仅返回1行时过滤掉结果)

相关问题