tb 结构
insert into tb(id,name,cId) values('01','商品类1', '_B7293723-6F39-4B43-9CCF-840E21C8AF62')
insert into tb(id,name,cId) values('0101','商品类101', '_c7292372-6F39-4B43-9CCF-840E21C8AF62')
insert into tb(id,name,cId) values('0102','商品类102', '_f72df723-6F39-4B43-9CCF-840E21C8AF63')
insert into tb(id,name,cId) values('02','商品类2', '_e7293723-6F39-4B43-9CCF-2320E2C8AF62')
insert into tb(id,name,cId) values('0201','商品类201', '_a7df9372-6F39-4B43-9CCF-7840E218AF62')
insert into tb(id,name,cId) values('0203','商品类202' ,'_pB72f373-6F39-4243-9CCF-841E21C8AF62')
insert into tb(id,name,cId) values('020301','商品类20201','_B729df23-6F39-4B43-9CCF-840E21b3AF62')
insert into tb(id,name,cId) values('020302','商品类20202','_B8993723-6F39-4B43-9CCF-840E21C8AF62')
要一个select查询取得上级的cid如商品类101的上级的Cid='_B7293723-6F39-4B43-9CCF-840E21C8AF62'
生成如:id,name,cid,Pid，如何写？
------解决方案--------------------------------------------------------
- SQL code
Create Cursor tb (Id c(10),Name c(10),cid c(50))Insert Into tb(Id,Name,cId) Values('01','商品类1', '_B7293723-6F39-4B43-9CCF-840E21C8AF62')Insert Into tb(Id,Name,cId) Values('0101','商品类101', '_c7292372-6F39-4B43-9CCF-840E21C8AF62')Insert Into tb(Id,Name,cId) Values('0102','商品类102', '_f72df723-6F39-4B43-9CCF-840E21C8AF63')Insert Into tb(Id,Name,cId) Values('02','商品类2', '_e7293723-6F39-4B43-9CCF-2320E2C8AF62')Insert Into tb(Id,Name,cId) Values('0201','商品类201', '_a7df9372-6F39-4B43-9CCF-7840E218AF62')Insert Into tb(Id,Name,cId) Values('0203','商品类202' ,'_pB72f373-6F39-4243-9CCF-841E21C8AF62')Insert Into tb(Id,Name,cId) Values('020301','商品类20201','_B729df23-6F39-4B43-9CCF-840E21b3AF62')Insert Into tb(Id,Name,cId) Values('020302','商品类20202','_B8993723-6F39-4B43-9CCF-840E21C8AF62')Select a.* From tb a Inner Join tb b On Rtrim(a.Id)==Left(b.Id,Len(Rtrim(b.Id))-2) Where b.Name='商品类101'*-- orSelect a.* From tb a Left Join tb b On Rtrim(a.Id)==Left(b.Id,Len(Rtrim(b.Id))-2) Where b.Name='商品类101'*-- orSelect a.* From tb a Where Rtrim(a.Id) $ (Select Left(b.Id,Len(Rtrim(b.Id))-2) From tb b Where b.Name='商品类101') && vfp 9.0