代码之家 › 专栏 › 技术社区 › user8512043

日期差异和ORA-00937-非单个组功能

oracle10g oracle sql

user8512043 · 技术社区 · 8 年前

我正在尝试添加两个表,并获得两个日期的总天数。但有以下简单问题: 不是单个组组功能 .

这就是我迄今为止所尝试的:

SELECT COUNT(status) AS "Present Days",  
       (SELECT TRUNC(TO_DATE('01/10/2018', 'MM/DD/YYYY') - TO_DATE(k.JOINING_DATE, 'MM/DD/YYYY')) 
        FROM attendance m
             INNER JOIN EMP_OFFICIAL k 
             ON k.EMPNO = m.EMPNO 
        WHERE m.empno='EMP00254' 
        AND m.status='P') AS "Total Days" 
FROM attendance 
WHERE empno = 'EMP00254' 
AND status = 'P';

我可以不用双轨车吗?

3 回复 | 直到 8 年前

Boneist 8 年前

首先,该子查询很可能不是标量查询(看起来您假设考勤表中可以有多行),其次,它包含不必要的联接。。

我要做的是:

select count(*) as "Present Days",
       to_date('01/10/2018', 'mm/dd/yyyy') - k.joining_date "Total Days"
from   attendance a
       inner join emp_official k on a.empno = k.empno
where  a.empno = 'EMP00254'
and    a.status = 'P'
group by to_date('01/10/2018', 'mm/dd/yyyy') - k.joining_date;

这确实假设 k.joining_date 列为日期数据类型,“k.empno”是唯一的列。

MT0 8 年前

如果中只有一行 EMP_OFFICIAL 对于每个员工,您可以执行以下操作:

SELECT COUNT(a.status) AS "Present Days",  
       TRUNC( SYSDATE ) - TRUNC( MIN( k.joining_date ) ) AS "Total Days" 
FROM   attendance a
       RIGHT OUTER JOIN EMP_OFFICIAL k
       ON ( k.EMPNO = a.EMPNO )
WHERE  a.empno = 'EMP00254' 
WHERE  a.status = 'P';

也可以在子查询中进行聚合:

SELECT a."Present Days",  
       TRUNC( SYSDATE ) - TRUNC( k.joining_date ) AS "Total Days" 
FROM   (
         SELECT EMPNO,
                COUNT( status ) AS "Present Days"
         FROM   attendance
         WHERE  EMPNO = 'EMP00254'
         AND    status = 'P'
         GROUP BY EMPNO
       ) a
       RIGHT OUTER JOIN EMP_OFFICIAL k
       ON ( k.EMPNO = a.EMPNO )
WHERE  k.EMPNO = 'EMP00254';

或者你可以使用 UNION ALL 要查询这两个表并将结果分为两行(而不是两列),请执行以下操作:

SELECT 'Present Days' As type,
       COUNT(status)  AS Days
FROM   attendance
WHERE  empno = 'EMP00254' 
WHERE  status = 'P'
UNION ALL
SELECT 'Total Days',
       TRUNC( SYSDATE ) - TRUNC( joining_date )
FROM   EMP_OFFICIAL
WHERE  empno = 'EMP00254';

Barbaros Özhan 8 年前

要么删除 COUNT(status) AS "Present Days" 零件和 SELECT 内部偏执:

SELECT (TRUNC(TO_DATE('01/10/2018', 'MM/DD/YYYY') - TO_DATE(k.JOINING_DATE, 'MM/DD/YYYY')) "time difference" FROM attendance m
INNER JOIN EMP_OFFICIAL k ON k.EMPNO = m.EMPNO where m.empno='EMP00254' and m.status='P') AS "Total Days" 
 FROM attendance where empno = 'EMP00254' and status = 'P';

或

使用以下内容(添加 GROUP BY sql末尾的表达式):

SELECT COUNT(status) AS "Present Days",  
(TRUNC(TO_DATE('01/10/2018', 'MM/DD/YYYY') - TO_DATE(k.JOINING_DATE, 'MM/DD/YYYY')) "time difference" FROM attendance m
INNER JOIN EMP_OFFICIAL k ON k.EMPNO = m.EMPNO where m.empno='EMP00254' and m.status='P') AS "Total Days" 
 FROM attendance where empno = 'EMP00254' and status = 'P'
GROUP BY (TRUNC(TO_DATE('01/10/2018', 'MM/DD/YYYY') - TO_DATE(k.JOINING_DATE, 'MM/DD/YYYY'));

自从 groupped 和 non-groupped 项目不能一起使用。

顺便说一下,您不需要在内联select语句中使用dual,如 ( SELECT TRUNC(TO_DATE('01/10/2018', 'MM/DD/YYYY') - TO_DATE(k.JOINING_DATE, 'MM/DD/YYYY') FROM DUAL ) 只是 ( TRUNC(TO_DATE('01/10/2018', 'MM/DD/YYYY') - TO_DATE(k.JOINING_DATE, 'MM/DD/YYYY')) 足够了。