因为牵涉的表连接太多了,就只用的临时表作为查询主表
临时表t有product_id,year,product_name,plan_name,batch_name,first_login_time,
is_graduated,is_degree,product_type_code这几个字段,我要根据不同条件来统计不同的人数,
我的统计sql如下:
select PRODUCT_ID,YEAR,PRODUCT_NAME,PLAN_NAME,BATCH_NAME,nvl(count(*),'0') APPLY_NUM,'高等学历提升' as PRODUCT_CATEGORY_NAME,PRODUCT_TYPE_CODE
,nvl((
select count(1) from t where t.FIRST_LOGIN_DATE is not null group by YEAR,PRODUCT_ID having t.YEAR=YEAR and t.PRODUCT_ID=PRODUCT_ID
),'0') as ATTEND_NUM
,nvl((
select count(1) from t where t.IS_GRADUATED='是' group by YEAR,PRODUCT_ID having t.YEAR=YEAR and t.PRODUCT_ID=PRODUCT_ID
),'0') as GRADUATE_NUM
,nvl((
select count(1) from t where t.IS_DEGREE='是' group by YEAR,PRODUCT_ID having t.YEAR=YEAR and t.PRODUCT_ID=PRODUCT_ID
),'0') as DEGREE_NUM
from t
group by PRODUCT_ID,YEAR,PRODUCT_NAME,PLAN_NAME,BATCH_NAME,PRODUCT_TYPE_CODE
order by YEAR desc
其中YEAR和PRODUCT_ID为查询出来的值再传入统计字段过滤,现在问题是YEAR和PRODUCT_ID无法传入,这样就一直报一对多的错误,求正确的sql的写法。OracleSQL
临时表t有product_id,year,product_name,plan_name,batch_name,first_login_time,
is_graduated,is_degree,product_type_code这几个字段,我要根据不同条件来统计不同的人数,
我的统计sql如下:
select PRODUCT_ID,YEAR,PRODUCT_NAME,PLAN_NAME,BATCH_NAME,nvl(count(*),'0') APPLY_NUM,'高等学历提升' as PRODUCT_CATEGORY_NAME,PRODUCT_TYPE_CODE
,nvl((
select count(1) from t where t.FIRST_LOGIN_DATE is not null group by YEAR,PRODUCT_ID having t.YEAR=YEAR and t.PRODUCT_ID=PRODUCT_ID
),'0') as ATTEND_NUM
,nvl((
select count(1) from t where t.IS_GRADUATED='是' group by YEAR,PRODUCT_ID having t.YEAR=YEAR and t.PRODUCT_ID=PRODUCT_ID
),'0') as GRADUATE_NUM
,nvl((
select count(1) from t where t.IS_DEGREE='是' group by YEAR,PRODUCT_ID having t.YEAR=YEAR and t.PRODUCT_ID=PRODUCT_ID
),'0') as DEGREE_NUM
from t
group by PRODUCT_ID,YEAR,PRODUCT_NAME,PLAN_NAME,BATCH_NAME,PRODUCT_TYPE_CODE
order by YEAR desc
其中YEAR和PRODUCT_ID为查询出来的值再传入统计字段过滤,现在问题是YEAR和PRODUCT_ID无法传入,这样就一直报一对多的错误,求正确的sql的写法。OracleSQL
解决方案 »
- 一条sql语句优化的问题?
- Linux下安装ORACLE9I问题,解决即结贴
- PL/SQL的块中可以切换用户登录吗?
- 请教关于竞价系统数据库设计的问题
- PL/SQL學習
- UTL_FILE.get_line读取数据的问题
- 没有做任何的操作,但回滚空间一直都很大,怎么解决啊,
- 急救,imp导入数据时提示“插入的值对于列过大”。
- 无法启动OracleOraHome81ManagerServer 服务?
- 请问用什么sql可以查询有哪些数据库?
- ORA-00376: file 7 cannot be read at this time ;help!求救!
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
我这样写就可以传入值,但是红色部分不那么写就无法传入了,现在都还没弄懂什么原因
select xl.PLAN_NAME,xl.BATCH_NAME,xl.PRODUCT_NAME,nvl(count(1),'0') APPLY_NUM,xl.YEAR,xl.PRODUCT_ID
,nvl((
select count(1) from xl t where t.FIRST_LOGIN_DATE is not null and t.year = xl.year and t.product_id=xl.product_id group by YEAR,PRODUCT_ID
),'0') as ATTEND_NUM
,nvl((
select count(1) from xl t where t.IS_GRADUATED='是' and t.year = xl.year and t.product_id=xl.product_id group by YEAR,PRODUCT_ID
),'0') as GRADUATE_NUM
,nvl((
select count(1) from xl t where t.IS_DEGREE='是' and t.year = xl.year and t.product_id=xl.product_id group by YEAR,PRODUCT_ID
),'0') as DEGREE_NUM
from xl
group by xl.PLAN_NAME,xl.BATCH_NAME,xl.PRODUCT_NAME,xl.YEAR,xl.PRODUCT_ID
order by xl.year desc