在查询某个月的登录用户数时有时会出现错误 ORA-01427: 单行子查询返回多个行,sql如下:
select NVL((select count(distinct user_id) as actnum
from (select user_id, to_char(login_date, 'YYYY-MM') riqi
from log_login where login_date between
to_date('2018-02-01', 'YYYY-MM-DD hh24:mi:ss') and
to_date('2018-02-28', 'YYYY-MM-DD hh24:mi:ss') and
user_oth_id is null
group by to_char(login_date, 'YYYY-MM'), user_id) t
group by t.riqi), 0) from dual
请问有谁指导报错原因吗,哪个子查询可能会返回多个行?
æ¥è¯¢æ¯æ¬¡é½æ¯æææ¥è¯¢çï¼group by æå¹´æåç»ï¼åç»åcountç»è®¡è¯¥åç»çç¨æ·æ°ï¼åºè¯¥ä¼å¾å°ä¸ä¸ªYYYYMMæçç»å½ç¨æ·æ°ï¼åæä¹ä¼åºç°å¤è¡å¢ï¼
追çå¦æç¡®å®åªæ¥è¯¢ä¸ä¸ªæèå´å
çæ°æ®ï¼é£ä¹åæ¥è¯¢ä¸çgroup by t.riqiå°±å¯ä»¥ä¸è¦äºï¼ç´æ¥count就好äºã
ä¼åºç°å¤è¡æ°æ®ï¼é®é¢å°±æ¯åºå¨group by t.riqiè¿éã
å¦æå¯ä»¥è¿½è¸ªçè¯ï¼å¯ä»¥ænvlä¸çåæ¥è¯¢ç¬ç«å¼æ¥æ§è¡ä¸ä¸ï¼åºè¯¥å¯ä»¥åç°é®é¢å°±å¨é£éã
çå®æ°æ®æ¥è§¦ä¸å°ï¼æ æ³è¿½è¸ªï¼èªå·±ç¨æµè¯æ°æ®æµè¯çæ¶ååä¸ä¼åºç°è¿ä¸ªé®é¢ï¼æ以ä¸å¤ªå¥½æ¾é®é¢åå