引申:一对多关系的问题求助,子父表关系
昨天看了“一对多关系的问题求助”,其实就是子父表关系,N年之前,我学PHP写论坛的时候,用递归函数来处理,今天重温功课,不用递归,用查询语句,重点在ID3,好像是记录了父子链,通过这个链,来找到相关记录,ID2好像是没用了?其实还是有用的。缺点,好像是牺牲了空间,毕竟多了个ID3,长度还蛮不少的。欢迎大家讨论。多谢“吹水佬”提示用INLIST()函数。CLEAR
CREATE CURSOR t1(id1 c(10),id2 c(10),name c(20),id3 c(110))
INSERT INTO t1(id1,id2,name,id3) values("A001","","冰箱","A001")
INSERT INTO t1(id1,id2,name,id3) values("A002","","电脑","A002")
INSERT INTO t1(id1,id2,name,id3) values("A003","A001","华楞冰箱","A001.A003")
INSERT INTO t1(id1,id2,name,id3) values("A004","A003","华楞冰箱压缩机","A001.A003.A004")
INSERT INTO t1(id1,id2,name,id3) values("A005","","手机","A005.")
INSERT INTO t1(id1,id2,name,id3) values("A006","A005","苹果手机","A005.A006")
INSERT INTO t1(id1,id2,name,id3) values("A007","A004","华楞冰箱压缩机电容","A001.A003.A004.A007")
*查所有记录
SELECT * FROM t1 ORDER BY id3
*查子记录,包括自己
id="A004"
SELECT * FROM t1 WHERE id3 like '%'+id+'%' ORDER BY id3
*查父记录,包括自己
id="A004"
SELECT ID3 FROM T1 WHERE ID1=ID INTO ARRAY AryId
*把ID3变为INLIST()函数列表参数格式,"A001.A003.A004" 变为 'A001','A003','A004'
FID=""
DO WHILE(AT(".",AryId)>0)
FID=IIF(EMPTY(FID),"'"+SUBSTR(AryId,1,AT(".",AryId)-1)+"'",FID+","+"'"+SUBSTR(AryId,1,AT(".",AryId)-1)+"'")
AryId=SUBSTR(AryId,AT(".",AryId)+1)
ENDDO
FID=IIF(EMPTY(fid),"'"+id+"'",fid+","+"'"+id+"'")
SELECT * FROM t1 WHERE INLIST(id1,&Fid) ORDER BY id3