SELECT wap_user.u_name, NVL (f_pv, 0), NVL (m_pv, 0), NVL (f_se, 0),
NVL (m_se, 0)
FROM (SELECT *
FROM (SELECT user_id AS fuser1, COUNT (1) AS f_pv
FROM wap_vlog
WHERE v_mobile_flag = 0
AND user_id IS NOT NULL
AND wap_vlog.cdate < 1167451200
AND wap_vlog.cdate > 1136044800
GROUP BY user_id)
FULL JOIN
(SELECT user_id AS fuser2, COUNT (1) AS m_pv
FROM wap_vlog
WHERE v_mobile_flag = 1
AND user_id IS NOT NULL
AND wap_vlog.cdate < 1167451200
AND wap_vlog.cdate > 1136044800
GROUP BY user_id) ON fuser1 = fuser2
) t1
FULL JOIN
(SELECT *
FROM (select tt2 AS suser1,count(1) as f_se from(SELECT session_id tt1 , user_id tt2
FROM wap_vlog
WHERE v_mobile_flag = 0
AND session_id IS NOT NULL
AND user_id IS NOT NULL
AND wap_vlog.cdate < 1167451200
AND wap_vlog.cdate > 1136044800
GROUP BY session_id,user_id)group by tt2)
FULL JOIN
(select tt4 AS suser2,count(1) as m_se from ( SELECT session_id tt3 , user_id tt4
FROM wap_vlog
WHERE v_mobile_flag = 1
AND session_id IS NOT NULL
AND user_id IS NOT NULL
AND wap_vlog.cdate < 1167451200
AND wap_vlog.cdate > 1136044800
GROUP BY session_id, user_id)group by tt4) ON suser1 = suser2
) t2 ON t1.fuser1 = t2.suser1
, wap_user
WHERE wap_user.domain_id =2 and (wap_user.ID = t1.fuser1
OR wap_user.ID = t1.fuser2
OR wap_user.ID = suser1
OR wap_user.ID = suser2 )
ORDER BY NVL (f_pv, 0) DESC
迄今为止,我写过的最长的SQL语句
最新推荐文章于 2023-09-12 17:23:55 发布