有一简化的表如下:
Account Month Day
aa 2 2
aa 2 2
aa 2 3
bb 2 4
cc 1 21现在想选出比如2月的登陆帐户次数,同一天登陆多次的只计算一次,即选出的结果要如下:
Account LoginTimes Month
aa 2 2
bb 1 2
cc 0 2aa在2月2日登陆了两次,但只算一次请问这个SQL语句要怎么写啊,请赐教!
非常感谢您的帮助!
Account Month Day
aa 2 2
aa 2 2
aa 2 3
bb 2 4
cc 1 21现在想选出比如2月的登陆帐户次数,同一天登陆多次的只计算一次,即选出的结果要如下:
Account LoginTimes Month
aa 2 2
bb 1 2
cc 0 2aa在2月2日登陆了两次,但只算一次请问这个SQL语句要怎么写啊,请赐教!
非常感谢您的帮助!
select account,count(account) logintimes,month
from
(
select account,month,day
from aa
where month='2'
group by account,month,day)
group by account,month
Account,
count(distinct day) LoginTimes,
Month
from tablename
group by Account,Month
你需要哪月份就过滤哪月份
create table test_ilenso
(Account varchar2(20),Month number(2),Day number(2));insert into ...SQL> select distinct a.account,nvl(b.LoginTimes,0) LoginTimes,nvl(b.month,2) month
2 From test_ilenso a
3 left join (select Account,count(distinct day) LoginTimes,Month
4 from test_ilenso where month=2 group by Account,Month) b
5 on a.account=b.account and a.month=b.month;ACCOUNT LOGINTIMES MONTH
-------------------- ---------- ----------
aa 2 2
bb 1 2
cc 0 2