网站的用户登录网站的月统计次数，每天登录多次算一次
--VIEW DWUSER.LOGIN_MYONSTAR_MONTH: 供应商逻辑
select count(*) from (--直接COUNT出来月访问量
select trunc(OCCUR_DATE),user_id,count(user_id) from T_SODW_ACTIVITY_LOG --每一个人每天的一条记录
where usertype = 'subscriber'
AND result = 'Success'
AND occur_date>=to_date('2011-08-01','YYYY-MM-DD')
and OCCUR_DATE<TO_DATE('2011-09-01','YYYY-MM-DD')
group by trunc(OCCUR_DATE),user_id order by trunc(OCCUR_DATE) desc
)
VIEW DWUSER.LOGIN_MYONSTAR_MONTH:
select substr(tab.aday,1,7),sum(E_mail) from(--让每一天的访问量相加成为总量
SELECT TO_CHAR (OCCUR_DATE, 'yyyy-mm-dd') aday,COUNT (DISTINCT user_id) E_mail --先取每天的访问量
FROM T_SODW_ACTIVITY_LOG
WHERE usertype = 'subscriber'
AND TO_CHAR (OCCUR_DATE, 'mm') <>
TO_CHAR (SYSDATE, 'mm')
AND user_id LIKE '%@%'
AND result = 'Success'
GROUP BY TO_CHAR (OCCUR_DATE, 'yyyy-mm-dd')
) tab
group by substr(tab.aday,1,7)
----------------------------------------------------------------------------------------------------------------------------------------------------------
JOIN用法在多表之间连接
SELECT t1.*
FROM t1 LEFT JOIN t2
ON t1.order_number = t2.order_number
INNER JOIN t3
ON t2.order_number_seq = t3.order_number_seq