sql - 在 Oracle SQL 中使用 CTE（ORA-00923：在预期的地方找不到 FROM 关键字）

Question

问题：从下表包含订购日期和项目的列表，编写一个查询以返回每个日期最常订购的项目。在平局的情况下返回多个项目。

Oracle SQL 中的问题输入

create table items(dates varchar2(200), item VARCHAR2(200));

insert into items values ('01-01-20', 'apple');
insert into items values ('01-01-20', 'apple');
insert into items values ('01-01-20', 'pear');
insert into items values ('01-01-20', 'pear');
insert into items values ('01-02-20', 'pear');
insert into items values ('01-02-20', 'pear');
insert into items values ('01-02-20', 'pear');
insert into items values ('01-02-20', 'orange');

这是我要执行的代码。但是，我收到一个错误

ORA-00923: 在预期的地方找不到 FROM 关键字

有人可以帮忙吗？

select dates, item
from 
(
SELECT *, rank() OVER (PARTITION by dates ORDER BY item_count DESC) AS date_rank
FROM 
(
SELECT dates, item, count(*) AS item_count
FROM items
GROUP BY 1, 2
ORDER BY 1))
where date_rank=1;

标签： sqloraclecommon-table-expressionwindow-functions

Answer 1

我已*在您的子查询中替换为dates, items并将 group by 子句从group by 1,2to更改为group by dates, items。它正在工作。

但我建议使用 query#2，它在我看来更具可读性并且将来易于更改。

 create table items(dates varchar2(200), item VARCHAR2(200));

 insert into items values ('01-01-20', 'apple');

 insert into items values ('01-01-20', 'apple');

 insert into items values ('01-01-20', 'pear');

 insert into items values ('01-01-20', 'pear');

 insert into items values ('01-02-20', 'pear');

 insert into items values ('01-02-20', 'pear');

 insert into items values ('01-02-20', 'pear');

 insert into items values ('01-02-20', 'orange');

查询#1：

select dates, item
     from 
     (
     SELECT dates,item, rank() OVER (PARTITION by dates ORDER BY item_count DESC) AS date_rank
     FROM 
     (
     SELECT dates, item, count(*) AS item_count
     FROM items
     GROUP BY dates, item
     ORDER BY dates) ) 
     where date_rank=1;

输出：

日期	物品
01-01-20	苹果
01-01-20	梨
01-02-20	梨

查询#2：

 with cte (dates,item,date_rank) as
 (  
    SELECT dates, item,rank() OVER (PARTITION by dates ORDER BY count(*) DESC)  AS date_rank
    FROM items
    GROUP BY dates, item
    ORDER BY dates
 )
 select  dates,item from cte 
 where date_rank=1;

输出：

日期	物品
01-01-20	苹果
01-01-20	梨
01-02-20	梨

db<小提琴在这里