迄今为止，我写过的最长的SQL语句

最新推荐文章于 2025-05-11 01:05:45 发布

最新推荐文章于 2025-05-11 01:05:45 发布 · 353 阅读

本文介绍了一个复杂的SQL查询案例，该查询用于从wap_vlog表中统计不同条件下的用户访问频次和会话次数，并关联了wap_user表以获取用户名信息。查询涉及多个子查询和表连接操作，展示了如何通过SQL进行数据汇总和分析。

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