常用查询_增量增加

----多表查询
---笛卡尔集
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';