在多級分銷體系中
如何通過一個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&""
