各位达人,请教一个问题,有一张表,是记录用户交易的,为了简化问题,假设只有两个字段,一个是 日期,一个是帐号,比如TradingDate Account
2010-01-01 Jack
2010-06-05 Mary
2010-08-01 Frank
...
...现在要查询 2010年中每个月都有交易的用户数。我现在能想到的写法是Select count(*)
From (Select distinct Account
From Table
Where TradingDate Between '2011-01-01' And '2011-01-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-02-01' And '2011-02-28'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-03-01' And '2011-03-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-04-01' And '2011-04-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-05-01' And '2011-05-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-06-01' And '2011-06-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-07-01' And '2011-07-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-08-01' And '2011-08-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-09-01' And '2011-09-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-10-01' And '2011-10-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-11-01' And '2011-11-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-12-01' And '2011-12-31')
但是我觉得太繁琐了,而且效率也不高。在这里请教达人,能不能指点一下,给一个高效简洁的写法。万分感谢!
2010-01-01 Jack
2010-06-05 Mary
2010-08-01 Frank
...
...现在要查询 2010年中每个月都有交易的用户数。我现在能想到的写法是Select count(*)
From (Select distinct Account
From Table
Where TradingDate Between '2011-01-01' And '2011-01-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-02-01' And '2011-02-28'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-03-01' And '2011-03-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-04-01' And '2011-04-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-05-01' And '2011-05-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-06-01' And '2011-06-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-07-01' And '2011-07-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-08-01' And '2011-08-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-09-01' And '2011-09-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-10-01' And '2011-10-31'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-11-01' And '2011-11-30'
Intersect
Select distinct Account
From Table
Where TradingDate Between '2011-12-01' And '2011-12-31')
但是我觉得太繁琐了,而且效率也不高。在这里请教达人,能不能指点一下,给一个高效简洁的写法。万分感谢!
select substr(TradingDate,1,7), count(distinct Account) from t
where TradingDate like '2010-%')
group by substr(TradingDate,1,7)
若TradingDate为日期型,用日期函数的TRANC(date)取年月,类似。。
我要的是每个月都有交易的用户,不是 每个月的交易用户数。您给的 sql 不对。
where TradingDate like '2010-%'
and account in (
select account from (select account, 1 cc
from t
group by account,substr(TradingDate,1,7))
group by account
having sum(cc) = 12 )
group by substr(TradingDate,1,7)
where TradingDate like '2010-%'
and account in (
select account from (select account, 1 cc
from t where TradingDate like '2010-%'
group by account,substr(TradingDate,1,7))
group by account
having sum(cc) = 12 )
group by substr(TradingDate,1,7)
select ACCOUT
from (select ACCOUT, trunc(tradingdate, 'MM') tradingdate
from t2
group by ACCOUT, trunc(tradingdate, 'MM'))
group by to_char(tradingdate, 'yyyy'), ACCOUT
having count(*) = 12