ORA-00904：“TOTAL_ORDERS”：标识符无效-Java 学习之路

我目前正在尝试计算每个郊区的订单，这些订单的交付时间晚于一个月的要求，以及他们迟到的平均时间 .

到目前为止，我已经完成了这个，没有参数查询，用户输入他们希望看到的月份 .

SELECT s.suburbname, 
        Count(*) AS total_orders ,
        ("ORDER".ACTUALTIMEDELIVERED - "ORDER".CUSTREQTIME)/total_orders AS latetime
FROM   "ORDER" 
left join restaurant r 
       ON "ORDER".restaurantid = r.restaurantid 
left join suburb s 
       ON r.suburbid = s.suburbid  
       WHERE   "ORDER".deliveredflag = 'X' 
       AND "ORDER".ACTUALTIMEDELIVERED >  "ORDER".CUSTREQTIME 
GROUP  BY s.suburbname 
ORDER  BY total_orders, latetime;

然而，它给我一个ORA-00904：“TOTAL_ORDERS”：无效的标识符 .

这是我的分组条款吗？

Update 1 我似乎总结了INTERVAL DAY TO SECOND数据类型，因为它抛出了我的错误：ORA-00932：不一致的数据类型：预期NUMBER得到INTERVAL DAY TO SECOND .

有没有及时总结所有的时间差异并将其平均化？

Update 2

我已经设法通过使用EXTRACT函数来解决我的所有问题，提取小时和分钟，并将所有这些问题与计数相加 .

SELECT 

s.suburbname, 
       Count(*) AS total_orders,

SUM(60*(Extract(hour from "ORDER".ACTUALTIMEDELIVERED) - Extract(hour from "ORDER".Custreqtime)) + Extract(minute from "ORDER".ACTUALTIMEDELIVERED) - Extract(minute from "ORDER".Custreqtime))as amt


FROM   "ORDER" 
       left join restaurant r 
              ON "ORDER".restaurantid = r.restaurantid 
       left join suburb s 
              ON r.suburbid = s.suburbid 
WHERE  
"ORDER".deliveredflag = 'X'
AND
"ORDER".ACTUALTIMEDELIVERED > "ORDER".CUSTREQTIME
GROUP  BY s.suburbname
ORDER  BY s.suburbname desc;

2 回答

您无法在计算中重复使用 total_orders ：

SELECT s.suburbname, Count(*) total_orders ,
("ORDER".ACTUALTIMEDELIVERED - "ORDER".CUSTREQTIME)/count(*) latetime     
FROM   "ORDER" 
left join restaurant r 
...

希望能帮助到你

回复于 2024-04-29T17:22:06+08:00

问题出在这一行 ("ORDER".ACTUALTIMEDELIVERED - "ORDER".CUSTREQTIME)/total_orders AS latetime .

Total_order 只是之前在行中定义的列别名 . 而第二个隐藏的例外是 ORA-00979: not a GROUP BY expression.

您可能应该在此查询中使用分析函数 . 但我不知道业务需求 .

select s.suburbname
       , count(*) over( partition by s.suburbname) as total_orders

       , ("ORDER".ACTUALTIMEDELIVERED - "ORDER".CUSTREQTIME) /  count(*) over( partition by s.suburbname) as latetime
    from "ORDER"
         left join restaurant r on "ORDER".restaurantid = r.restaurantid
         left join suburb s on r.suburbid = s.suburbid
   where "ORDER".deliveredflag = 'X' and "ORDER".ACTUALTIMEDELIVERED > "ORDER".CUSTREQTIME
order by total_orders, latetime;

回复于 2024-04-29T17:22:06+08:00

ORA-00904：“TOTAL_ORDERS”：标识符无效

2 回答

相关问题