先创建两个示例表,并插入测试数据。
source表:
插入数据
channel表:
插入数据
查询每个channel对应的访问数和注册数:
SELECT DISTINCT(channel_name) as cname, identification as iden,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="visit" and a.id=channel_id)as uv ,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="register" and a.id=channel_id) as reg
from source
RIGHT JOIN channel a
on a.id=channel_id;
结果为:
如果想要只查询测试1则在最后加个查询条件:
SELECT DISTINCT(channel_name) as cname, identification as iden,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="visit" and a.id=channel_id)as uv ,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="register" and a.id=channel_id) as reg
from source
RIGHT JOIN channel a
on a.id=channel_id
where channel_id=66;
结果:
想要在此基础上再加个合计,方法有两种,
第一种
在之前基础上再加上个合计的查询,拼起来
SELECT '所有' as cname, '-' as iden,
(select count(*) from source
where create_time >= '2018-06-12' and create_time < '2018-06-17' and type="visit" )as uv ,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="register" ) as reg
from source
GROUP BY cname;
加条件的话是这样:
SELECT '所有' as cname, '-' as iden,
(select count(*) from source
where create_time >= '2018-06-12' and create_time < '2018-06-17' and channel_id=66 and type="visit" )as uv ,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and channel_id=66 and type="register" ) as reg
from source
GROUP BY cname;
第二种:
select
case when c_name is not null then c_name else '所有渠道' end c_name,
sum(uv) as uvs,
sum(reg) as regs
from (SELECT DISTINCT(channel_name) as c_name,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="visit" and a.id=channel_id)as uv ,
(select count(*) from source
WHERE create_time >= '2018-06-12' and create_time < '2018-06-17' and type="register" and a.id=channel_id) as reg
from source b
RIGHT JOIN channel a
on a.id=channel_id ) as c
group by c_name
with rollup;
