常用查询_增量增加
上一篇 /
下一篇 2019-08-22 11:27:01
----多表查询
---笛卡尔集
select * from ccare.inf_subscriber_all,ccare.inf_customer_all;
----等值连接
select t1.sub_id,t2.cust_id,t2.cust_code from ccare.inf_subscriber_all t1,ccare.inf_customer_all t2 where t1.cust_id=t2.cust_id;
select t1.sub_id,t2.cust_id,t2.acct_id from ccare.inf_subscriber_all t1 left join ccare.inf_acct t2 on t1.cust_id=t2.cust_id;
----左外连接
select t.cust_id,t.sub_id,t1.cust_id from ccare.inf_subscriber_all t left join ccare.inf_customer_all t1 on t.cust_id=t.cust_id;
----group by和 having
----grouping sets 合并多个分组结果
----rollup cube 纵向和横向小计统计
---group by子句用于对查询结果进行分组统计,而having子句则用于限制分组显示结果
----统计同一个客户下有多少个用户
select count(t.sub_id), t.cust_id
from ccare.inf_subscriber_all t
group by t.cust_id
having count(t.sub_id) > 3;
----查询同一个客户下有超过3个用户的所有客户用户信息
select t1.*, t1.rowid
from ccare.inf_subscriber_all t1
where t1.cust_id in (select t.cust_id
from ccare.inf_subscriber_all t
group by t.cust_id
having count(t.sub_id) > 3)
and t1.sub_state = 'B02';
收藏
举报
TAG: