select ( case test.smallitemname when '%' then avg(qoil+coil+moil) as 合计 else qoil+coil+moil as 合计 end) qoil as 汽油,coil as 柴油,moil as 煤油 from test where bigitemname = '累计出厂完成总量' group by qoil,coil,moil;这句问题在哪呢?提示缺失关键字错误,请高手帮帮忙
调试欢乐多
(case test.smallitemname
when '%' then avg(qoil+coil+moil)
else qoil+coil+moil end) as 合计,
qoil as 汽油,
coil as 柴油,
moil as 煤油
from test
where bigitemname = '累计出厂完成总量'
group by qoil,coil,moil;
SMALLITEMNAME NVARCHAR2(50)
PROVINCE NVARCHAR2(50)
MATERIALOIL VARCHAR2(50)
QOIL NUMBER(10)
COIL NUMBER(10)
MOIL NUMBER(10)
(case test.smallitemname
when '%' then avg(qoil+coil+moil)
else qoil+coil+moil end) as 合计
...
group by qoil,coil,moil这句话原本就不对,意义不明
WHEN TEST.SMALLITEMNAME = '%' THEN
AVG(QOIL + COIL + MOIL)
ELSE
QOIL + COIL + MOIL
END) AS 合计,
QOIL AS 汽油,
COIL AS 柴油,
MOIL AS 煤油
FROM TEST
WHERE BIGITEMNAME = '累计出厂完成总量'
GROUP BY QOIL, COIL, MOIL;
这样试试
WHEN TEST.SMALLITEMNAME = '%' THEN
SUM(QOIL) + SUM(COIL) + SUM(MOIL)/3
ELSE
SUM(QOIL) + SUM(COIL) + SUM(MOIL)
END) AS 合计,
SUM(QOIL) AS 汽油,
SUM(COIL) AS 柴油,
SUM(MOIL) AS 煤油
FROM TEST
WHERE BIGITEMNAME = '累计出厂完成总量'
GROUP BY TEST.SMALLITEMNAME;
select
(case test.smallitemname
when '%' then avg(qoil+coil+moil)
else qoil+coil+moil end) as 合计,
qoil as 汽油,
coil as 柴油,
moil as 煤油
from test
where bigitemname = '累计出厂完成总量'
group by qoil,coil,moil,smallitemname 呵~~非常感谢大家的帮忙