下面这段语句为什么加上KINMU_GOKEI.TY_JIKAGAI就报00797的错??请大侠指点。浪哥在么?SELECT KINMU_GOKEI.TY_JIKAGAI
,TO_CHAR(Count(KINMU_DAIKYU.NENGETSU),'99.9')
FROM KINMU_MONTHLY
LEFT JOIN KINMU_GOKEI ON KINMU_GOKEI.IDCODE='861000' AND KINMU_GOKEI.KIGYO_CODE='1030' AND KINMU_GOKEI.NENGETSU=KINMU_MONTHLY.NENGETSU
LEFT JOIN KINMU_DAIKYU ON KINMU_DAIKYU.IDCODE='861000' AND KINMU_DAIKYU.KIGYO_CODE='1030' AND KINMU_DAIKYU.NENGETSU=KINMU_MONTHLY.NENGETSU
LEFT JOIN KINMU_DAITAI ON KINMU_DAITAI.IDCODE='861000' AND KINMU_DAITAI.KIGYO_CODE='1030' AND KINMU_DAITAI.NENGETSU=KINMU_MONTHLY.NENGETSU
WHERE KINMU_MONTHLY.IDCODE='861000' AND KINMU_MONTHLY.KIGYO_CODE='1030'
GROUP BY KINMU_DAIKYU.NENGETSU ORDER BY KINMU_DAIKYU.NENGETSU DESC ;
,TO_CHAR(Count(KINMU_DAIKYU.NENGETSU),'99.9')
FROM KINMU_MONTHLY
LEFT JOIN KINMU_GOKEI ON KINMU_GOKEI.IDCODE='861000' AND KINMU_GOKEI.KIGYO_CODE='1030' AND KINMU_GOKEI.NENGETSU=KINMU_MONTHLY.NENGETSU
LEFT JOIN KINMU_DAIKYU ON KINMU_DAIKYU.IDCODE='861000' AND KINMU_DAIKYU.KIGYO_CODE='1030' AND KINMU_DAIKYU.NENGETSU=KINMU_MONTHLY.NENGETSU
LEFT JOIN KINMU_DAITAI ON KINMU_DAITAI.IDCODE='861000' AND KINMU_DAITAI.KIGYO_CODE='1030' AND KINMU_DAITAI.NENGETSU=KINMU_MONTHLY.NENGETSU
WHERE KINMU_MONTHLY.IDCODE='861000' AND KINMU_MONTHLY.KIGYO_CODE='1030'
GROUP BY KINMU_DAIKYU.NENGETSU ORDER BY KINMU_DAIKYU.NENGETSU DESC ;
SELECT KINMU_GOKEI.TY_JIKAGAI
,TO_CHAR(Count(KINMU_DAIKYU.NENGETSU),'99.9')
FROM KINMU_MONTHLY
GROUP BY KINMU_DAIKYU.NENGETSU ORDER BY KINMU_DAIKYU.NENGETSU DESC ;
ID CLASSNO NAME
---------- ---------- ------------------
1 1 张三
2 1 李四
3 1 王五
4 2 刘六
5 2 钱七
6 2 赵八
已选择6行。问题1:查出一班的人数?
SQL> select count (*) from student where classno=1 group by classno;
COUNT(*)
----------
3问题2:查人数的同时能不能把学生姓名也查出来呢?SQL> select name ,count (*) from student where classno=1 group by classno;
select name ,count (*) from student where classno=1 group by classno
*
ERROR 位于第 1 行:
ORA-00979: 不是 GROUP BY 表达式可见这个语句满足不了我们的目的,因为我们分组后,我们是对组进行操作,像学生姓名这列不是组能有的性质。
, count(t.c2) over(partition by c2)
from t;查询按c2分组,同时显示不分组的c1
既然是要聚合,却没有对KINMU_GOKEI.TY_JIKAGAI进行聚合处理,所以要出错
根据需要,可以在KINMU_GOKEI.TY_JIKAGAI用函数max()进行处理,或者将KINMU_GOKEI.TY_JIKAGAI加到group by后面,根据KINMU_GOKEI.TY_JIKAGAI和KINMU_DAIKYU.NENGETSU分组
如果你要查询的count是根据KINMU_DAIKYU.NENGETSU分组得到,同时又要查询出聚合前其他字段的值,就要用到分析函数
SELECT KINMU_GOKEI.TY_JIKAGAI
,TO_CHAR(COUNT(KINMU_DAIKYU.NENGETSU)OVER(PARTITION BY KINMU_DAIKYU.NENGETSU),'99.9'),
KINMU_GOKEI.TY_JIKAGAI
FROM KINMU_MONTHLY
LEFT JOIN KINMU_GOKEI ON KINMU_GOKEI.IDCODE='861000' AND KINMU_GOKEI.KIGYO_CODE='1030' AND KINMU_GOKEI.NENGETSU=KINMU_MONTHLY.NENGETSU
LEFT JOIN KINMU_DAIKYU ON KINMU_DAIKYU.IDCODE='861000' AND KINMU_DAIKYU.KIGYO_CODE='1030' AND KINMU_DAIKYU.NENGETSU=KINMU_MONTHLY.NENGETSU
LEFT JOIN KINMU_DAITAI ON KINMU_DAITAI.IDCODE='861000' AND KINMU_DAITAI.KIGYO_CODE='1030' AND KINMU_DAITAI.NENGETSU=KINMU_MONTHLY.NENGETSU
WHERE KINMU_MONTHLY.IDCODE='861000' AND KINMU_MONTHLY.KIGYO_CODE='1030'
ORDER BY KINMU_DAIKYU.NENGETSU DESC