首页 新闻 搜索 专区 学院

ORA-00979: 不是 GROUP BY 表达式

0
[已解决问题] 解决于 2014-07-16 22:32

CREATE VIEW View_Fault
As
SELECT a.AppMonth, a.LineID, SUM(OffLines) OffLineSum, SUM(DepDelay + ArrDelay) NormalSum, SUM(SkipLine) SkipLineSum,
SUM(Clear) ClearSum,
 (SELECT COUNT(1)
FROM RunFault B
WHERE (DelayTypeID = 2) AND (LineID = A.LineID) AND (to_char(faultdate,'yyyyMM') = AppMonth)) DelayFiveSum
FROM View_FaultALL A

WHERE (nvl(IsDelete, 0) = 0) AND (Status = 1)
GROUP BY a.AppMonth, a.LineID

一直报错:去掉DelayFiveSum那一段就好了,难道DelayFiveSum这个子查询结果不被group by识别为聚合函数?我该怎么解决呢?

xyq_雨晴的主页 xyq_雨晴 | 初学一级 | 园豆:159
提问于:2014-07-16 16:53
< >
分享
最佳答案
0

根据你的SQL猜了下应该是ORACLE,得SQL如下

CREATE VIEW View_Fault
AS 
SELECT X.*, (
        SELECT COUNT(1)
        FROM RunFault B
        WHERE B.DelayTypeID = 2
            AND B.LineID = X.LineID
            AND to_char(B.faultdate, 'yyyyMM') = X.AppMonth
        ) AS DelayFiveSum
FROM (
    SELECT A.AppMonth, A.LineID, SUM(OffLines) AS OffLineSum, SUM(DepDelay + ArrDelay) AS NormalSum, SUM(SkipLine) AS SkipLineSum
        , SUM(Clear) AS ClearSum
    FROM View_FaultALL A
    WHERE nvl(IsDelete, 0) = 0
        AND Status = 1
    GROUP BY A.AppMonth, A.LineID
) X
奖励园豆:5
青 蛙 | 小虾三级 |园豆:655 | 2014-07-16 19:16
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册