在多级分销体系中
如何通过一个sj_id查询出自己的下级和上级的
包括自己
数据结构如下
huiyuan ‘会员表’
数据结构图
sql语句如下：
利用了Union连接查询 和嵌套查询
下级所有记录包括自己
select id,xm,hyjb_id from huiyuan where sj_id in (select id from huiyuan where sj_id=1) Union
select id,xm,hyjb_id from huiyuan where sj_id=1 Union
select id,xm,hyjb_id from huiyuan where sj_id in (select id from huiyuan where sj_id in (select id from huiyuan where sj_id=1))
Union select id,xm,hyjb_id from huiyuan where sj_id in (select id from huiyuan where sj_id in (select id from huiyuan where sj_id in (select id from huiyuan where sj_id=1)))
Union select id,xm,hyjb_id from huiyuan where sj_id in (select id from huiyuan where sj_id in (select id from huiyuan where sj_id in (select id from huiyuan where sj_id in (select id from huiyuan where sj_id=1))))
union select id,xm,hyjb_id from huiyuan where id=1
从而可以保证数据的完整性和唯一性
上级所有记录包括自己
select id,xm,hyjb_id from huiyuan where id= (select sj_id from huiyuan where id=250) Union
select id,xm,hyjb_id from huiyuan where id=250 Union
select id,xm,hyjb_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id=250))
Union select id,xm,hyjb_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id=250)))
Union select id,xm,hyjb_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id=(select sj_id from huiyuan where id=250))))
Union select id,xm,hyjb_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id= (select sj_id from huiyuan where id=(select sj_id from huiyuan where id=(select sj_id from huiyuan where id=250)))))
union select id,xm,hyjb_id from huiyuan where id=250
所有关系图
select * from hygxb where hy_gx_id in (select hy_gx_id from hygxb where hyjb_id>5 and hy_id=224 Union select hy_gx_id from hygxb where hy_gx_id=224 and hy_id=22) and hy_id<>224
delete from hygxb where hy_gx_id in (select hy_gx_id from hygxb where hyjb_id>"&yl_hyjb_id&" and hy_id="&hy_id&" Union select hy_gx_id from hygxb where hy_gx_id="&hy_id&" and hy_id="&yl_sj_id&") and hy_id<>"&hy_id&""
