有个表tab，测试数据如下：
ID,account_id,visit_time
1,l1@yahoo.cn,2012-02-15 00:12:23
2,l1@yahoo.cn,2012-02-15 10:12:23
3,l2@yahoo.cn,2012-02-15 00:11:45
4,l3@yahoo.cn,2012-02-15 03:01:12
5,l4@yahoo.cn,2012-02-15 16:21:38
6,l4@yahoo.cn,2012-02-15 11:20:52
我现在要用一条语句求出 2012-02-15 这天的账号只访问了1次的行数和所有的行数，
只访问了1次的有
3,l2@yahoo.cn,2012-02-15 00:11:45
4,l3@yahoo.cn,2012-02-15 03:01:12 这两行，总行数为6，所以结果为：
2,6
谁能帮我看下，有没有好的方法，最好是应用分析函数，谢谢大家了！
------解决方案--------------------
应该要2个查询子句才能解决，最和合并成一句而已。
第一个查询子句负责查询访问一次的行数，类似于：
Select COUNT(account_id)
From (
Select account_id
From tab
Where visit_timer = '2012-02-15' --要做时间转换
Order By account_id
Having COUNT(account_id) = 1
)
第二个查询子句负责查所有行数，以及再用个查询把两个结果合并成一行，这个就不复杂了吧。
------解决方案--------------------
- SQL code
select (select count(account_id) from (select account_id from tab where substr(visit_time,0,10) = '2012-02-15' group by account_id having count(account_id)=1)) o_num, (select count(0) from tab where substr(visit_time,0,10) = '2012-02-15') t_numfrom dual
------解决方案--------------------
- SQL code
select sum(decode(rcnt,1,1,0)), sum(rcnt)from(select account_id, max(rn) rcnt from(select account_id, row_number()over(partition by account_id order by id ) rn from tab where to_char(visit_time,'YYYY-MM-DD')='2012-02-15') group by account_id)
------解决方案--------------------
- SQL code
select (select count(account_id) from (select account_id from tab where substr(visit_time,0,10)='2012-02-15' group by account_id having count(account_id)=1)) o_num, (select count(0) from tab where substr(visit_time,0,10)='2012-02-15') t_numfrom dual
------解决方案--------------------
- SQL code
SELECT sum(CASE WHEN num=1 THEN 1 ELSE 0 END) Login_Once_Count,sum(num) Login_All_CountFROM (SELECT account_id,count(account_id) num FROM tab WHERE trunc(visit_time)=to_date('15-FEB-12','DD-MON-RR')GROUP BY account_id);
------解决方案--------------------
- SQL code
Plan hash value: 3708590803 ------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 3 (34)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 || 3 | HASH GROUP BY | | 1 | 17 | 3 (34)| 00:00:01 ||* 4 | TABLE ACCESS FULL| tab | 1 | 17 | 2 (0)| 00:00:01 |------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter(TRUNC(INTERNAL_FUNCTION("CREATED_AT"))=TO_DATE('17-FEB-12','DD-MON-RR'))Unable to gather statistics please unsure user has correct access.The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.
------解决方案--------------------