原来在聚鲨的练习题(实际操作)(已经理解了)
left join ,inner join , right join 的区别
主要需要理解的部分是:inner join 乐翻天join right join 的区别。二者之间的区别是以谁为主要的表,主要显示谁的问题。
表连接的方式
其次需要理解的是 select * from a inner join b on a.khdm = b.khdm inner join c on c.khdm = a.khdm 只要是可以继续下去的就可以 不断的写下去
加入一个固定的值的方式
第三个需要注意的是:我的可以在我想加入的渠道中显示任何我已经有的知识。如“北京” tb
select *
from (select distinct a.KHDM, a.zfl
from hqods.yg_res_ddyjb a
-- left join hqods.yg_ref_good_info b
-- on a.spbh = b.spbh
where a.zfl in ('纪念收藏品')
and a.khdj in ('紫金会员', '黄金会员', '普通会员','白金会员', '钻石会员')
--group by khdm, b.zfl
) m
inner join (select CUST_ID khdm, CUST_LVL_DESC 会员等级, province_nm
from hqods.yg_dim_customer h
where CUST_LVL_DESC in ('紫金会员',
'黄金会员',
'普通会员',
'白金会员',
'钻石会员')) x
on x.khdm = m.khdm
inner join (select e.customernumber,
u.name,
g.grpname,
e.bindstatus,
'北京' TB
from hqods.v_bf_crm_cp_customer e ---------------会员明细,绑定关系
inner join hqods.crm_sys_user u
on e.assignedusr = u.usrid
inner join hqods.crm_sys_uglink ug
on ug.usrid = u.usrid
inner join hqods.crm_sys_grp g
on g.grpid = ug.grpid
inner join (select g.grpid,
g.grpname,
g.parentid,
rownum rn
from hqods.crm_sys_grp g
where g.type = '1'
start with g.grpid = 'mgrLv'
connect by prior g.grpid = g.parentid) t1
on g.grpid = t1.grpid
inner join (select g.grpid,
g.grpname,
g.parentid,
rownum rn
from hqods.crm_sys_grp g
where g.type = '1'
start with g.grpid = 'AGENTGRP'
connect by prior g.grpid = g.parentid) t
on g.grpid = t.grpid
where e.bindstatus = 1) h
--left join (select grpname 组, td from hqods.v_yg_dim_dsjzb) e
-- on h.grpname = e.组
-- group by customernumber, name, grpname, td
-- ) f
on m.khdm = h.customernumber