SQL联表查询以及在查询的结果最后添加合计

本文介绍了一种使用SQL查询特定时间段内不同渠道的访问数和注册数的方法,并提供了如何针对特定渠道进行查询及添加合计数的示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

先创建两个示例表,并插入测试数据。

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;












评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值