java - 如果数据库(oracle)中没有记录，如何使用 spring jpa 与日期列结合显示零作为计数？

Question

我想按小时查询表中列值的交易计数，因为我想在图表中显示这些计数。

即使在没有记录的情况下，我也无法显示结果，在这种情况下我应该打印 0 计数，我已经尝试过

with tmpTable as 
(
    select 
        (minHourSeq + level-1) hourSeq 
    from 
        (select 
             min(extract (hour from TXN_DATE_TIME)) minHourSeq, 
             max(extract (hour from TXN_DATE_TIME)) maxHourSeq 
         from 
             TRANSACTION_REQUEST) v 
    connect by 
        (minHourSeq + level-1) <= maxHourSeq
)
select 
    a.hourSeq as hour, nvl(count(b.transaction), 0) as count 
from 
    TRANSACTION_REQUEST b, tmpTable a 
where 
    a.hourSeq = extract(hour from b.TXN_DATE_TIME(+)) 
group by 
    a.hourSeq 
order by 
    1;

当我在 Oracle SQL Developer 中执行它时它可以工作，但我收到错误

ORA-00907: missing right parenthesis

尝试用我的代码实现时

Query query = entityManager.createNativeQuery(
                "with tmpTable as (select (minHourSeq + level-1) hourSeq from (select min(extract (hour from txnDate)) minHourSeq, max(extract (hour from txnDate)) maxHourSeq from "
                        + TransactionRequest.class.getName()
                        + " ) v connect by (minHourSeq + level-1) <= maxHourSeq) "
                        + "select a.hourSeq as hour,nvl(count(b.transaction),0) as count from "
                        + TransactionRequest.class.getName()
                        + " b,tmpTable a a.hourSeq = extract(hour from b.txnDate(+)) group by a.hourSeq order by 1"); 

Answer 1

“其他解决方案”可能是将 CTE 移动到内联 View 中；也许您使用的工具无法识别它。像这样的事情:

  SELECT a.hourSeq AS hour, NVL (COUNT (b.transaction), 0) AS COUNT
    FROM TRANSACTION_REQUEST b,
         -- WITH factoring clause moved into an inline view
         (    SELECT (minHourSeq + LEVEL - 1) hourSeq
                FROM (SELECT MIN (EXTRACT (HOUR FROM TXN_DATE_TIME)) minHourSeq,
                             MAX (EXTRACT (HOUR FROM TXN_DATE_TIME)) maxHourSeq
                        FROM TRANSACTION_REQUEST) v
          CONNECT BY (minHourSeq + LEVEL - 1) <= maxHourSeq) a
   WHERE a.hourSeq = EXTRACT (HOUR FROM b.TXN_DATE_TIME(+))
GROUP BY a.hourSeq
ORDER BY 1;