SELECT COUNT(MPI.MOVIE_PLAYHIS_ID),MPI.TML_ID,MPI.MOVIE_ID,MPI.START_TIME,MPI.END_TIME,
MI.VIDEO_FORMAT,MI.VIDEO_LENGTH,MI.AUDIO_LENGTH,MI.MOVIE_TIME_LENGTH,
TML.CINEMA_ID,TML.GROUP_ID
FROM MOVIE_PLAYHIS_INFO MPI,MOVIE_INFO MI,IXTERMINAL TML
WHERE MPI.MOVIE_ID = MI.MOVIE_ID
AND MPI.TML_ID = TML.TML_ID
GROUP BY MPI.MOVIE_ID这个SQL有问题,COUNT()在查询的时候,只能显示COUNT列和按某某分组的那列(这里是movie_id)SELECT COUNT(MPI.MOVIE_PLAYHIS_ID),MPI.MOVIE_ID
FROM ......
GROUP BY MPI.MOVIE_ID(只按这个分组)
我想要下面效果SELECT COUNT(MPI.MOVIE_PLAYHIS_ID),MPI.MOVIE_ID,.....(这里添加其他查询显示字段)
FROM .......
GROUP BY MPI.MOVIE_ID(只按这个分组)
FROM .......
GROUP BY MPI.MOVIE_ID(只按这个分组)
--你的要求不符合语法
select子句中 没有用到聚合函数的字段一定要出现在group by子句中
--分析函数:
SELECT COUNT(MPI.MOVIE_PLAYHIS_ID)over(partition by MPI.MOVIE_ID),
MPI.MOVIE_ID,.....(这里添加其他查询显示字段)
FROM .......
select deptno,count(ename) over(partition by deptno) dept_cnt,ename from emp DEPTNO DEPT_CNT ENAME
------- ---------- ----------
10 3 CLARK
10 3 KING
10 3 MILLER
20 5 JONES
20 5 FORD
20 5 ADAMS
20 5 SMITH
20 5 SCOTT
30 6 WARD
30 6 TURNER
30 6 ALLEN
30 6 JAMES
30 6 BLAKE
30 6 MARTIN