UPDATE a SET SonCount=
(SELECT COUNT(1) FROM tb b WHERE CHARINDEX(',' + a.typeID, ','+b.typeID) AND a.typeID<>b.TypeID>0)
FROM tb aUPDATE tb SET SonCount=(SELECT COUNT(1) FROM tb WHERE typeID<>'0000') WHERE typeID='0000'你的根ID的值不对,应该是'000'才正确。
0000让人会理解为与 0001,0002是同一级的。
如果根ID是000那么第二个UPDATE语句是不必要的
(SELECT COUNT(1) FROM tb b WHERE CHARINDEX(',' + a.typeID, ','+b.typeID) AND a.typeID<>b.TypeID>0)
FROM tb aUPDATE tb SET SonCount=(SELECT COUNT(1) FROM tb WHERE typeID<>'0000') WHERE typeID='0000'你的根ID的值不对,应该是'000'才正确。
0000让人会理解为与 0001,0002是同一级的。
如果根ID是000那么第二个UPDATE语句是不必要的
(SELECT COUNT(1) FROM tb b WHERE CHARINDEX(',' + a.typeID, ','+b.typeID)>0 AND a.typeID<>b.TypeID)
FROM tb aUPDATE tb SET SonCount=(SELECT COUNT(1) FROM tb WHERE typeID<>'0000') WHERE typeID='0000'
CASE a.typeID WHEN '0000' THEN
(SELECT COUNT(1) FROM tb WHERE typeID<>'0000')
ELSE
(SELECT COUNT(1) FROM tb b WHERE CHARINDEX(',' + a.typeID, ','+b.typeID)>0 AND a.typeID<>b.TypeID)
END)
FROM tb a
CASE a.typeID WHEN '0000' THEN
(SELECT COUNT(1) FROM tb WHERE LEN(typeID)=4 AND typeID<>'0000')
ELSE
(SELECT COUNT(1) FROM tb b WHERE CHARINDEX(',' + a.typeID, ','+b.typeID)>0 AND LEN(a.typeID)+4=LEN(b.TypeID))
END)
FROM tb a
总结:/*计算树形结构表中每一栏目的一级子栏目数*/
UPDATE a SET SonCount=(SELECT COUNT(1) FROM ptype b WHERE b.parid=a.typeid)FROM ptype a
/*计算树形结构表中每一栏目的所有子栏目数*/
UPDATE a SET SonCount=(
CASE a.typeID WHEN '0000' THEN
(SELECT COUNT(1) FROM Ptype WHERE typeID<>'0000')
ELSE
(SELECT COUNT(1) FROM Ptype b WHERE CHARINDEX(',' + a.typeID, ','+b.typeID)>0 AND a.typeID<>b.TypeID)
END)
FROM Ptype a