通过跟踪语句如下:
**********************************************************************************************
CREATE TABLE #BasCst (tr_key varchar(254),tr_text varchar(254),tr_parent varchar(254), tr_where varchar(254))
INSERT INTO #BasCst
SELECT '0_' tr_key , '全部' tr_text , '' tr_parent , '1 > 0 '
UNION ALL SELECT '0000_','分类','0_','' UNION ALL
SELECT '0001_','虚拟结构','0_','' UNION ALL
SELECT '0000_01_','店铺性质','0000_','' UNION ALL
SELECT '0000_02_','按区域分','0000_','' UNION ALL
SELECT '0000_03_','按城市分','0000_','' UNION ALL
SELECT '0000_04_','按客户分','0000_','' UNION ALL
SELECT '0000_05_','按店铺等级分','0000_','' UNION ALL
SELECT '0001_'+RTRIM(shp_vis_id2)+'_',RTRIM(shp_vis_name2),'0001_',''
FROM bas_shp_vis GROUP BY shp_vis_id2,shp_vis_name2 UNION ALL
SELECT '0000_02_'+RTRIM(reg_id)+'_',RTRIM(reg_name),'0000_02_',
'cst_id in (SELECT cst_id FROM bas_shp
WHERE cit_id IN(SELECT cit_id FROM bas_city
WHERE reg_id='''+reg_id+''')) ' FROM bas_region
GROUP BY reg_id,reg_name UNION ALL SELECT '0000_03_'+RTRIM(cit_id)+'_',RTRIM(cit_name),'0000_03_',
'cst_id in (SELECT cst_id FROM bas_shp W
HERE cit_id='''+cit_id+''' )' FROM bas_city UNION ALL
SELECT '0000_04_'+RTRIM(cst_id)+'_',RTRIM(cst_name),
'0000_04_','cst_id in (SELECT cst_id FROM bas_shp
WHERE cst_id='''+cst_id+''' )' FROM bas_cst GROUP BY cst_id,cst_name UNION ALL
SELECT UPPER(LTRIM(RTRIM(ISNULL(NULLIF(shp_level,''),'EMPTY'))))
,UPPER(LTRIM(RTRIM(ISNULL(NULLIF(shp_level,''),'EMPTY')))) ,
'0000_05_' ,'cst_id in (SELECT cst_id from bas_shp where ' + CASE ISNULL(NULLIF(shp_level,''),'EMPTY')
WHEN 'EMPTY' THEN '(LTRIM(RTRIM(shp_level))=''EMPTY'' OR shp_level IS NULL))'
ELSE '(LTRIM(RTRIM(shp_level))='''+LTRIM(RTRIM(shp_level))+'''))' END
FROM bas_Shp group by shp_level UNION ALL
SELECT '1' tr_key , '自营专卖店' tr_text , '0000_01_' TrParent ,
'cst_id in (select cst_id from bas_shp where Shp_Kind = 1 ) ' UNION ALL
SELECT '2' tr_key , '自营专柜' tr_text , '0000_01_' TrParent ,
'cst_id in (select cst_id from bas_shp where Shp_Kind = 2 ) ' UNION ALL
SELECT '3' tr_key , '加盟专卖店' tr_text , '0000_01_' TrParent ,
' cst_id in (select cst_id from bas_shp where Shp_Kind = 3 ) ' UNION ALL
SELECT '4' tr_key , '加盟专柜' tr_text , '0000_01_' TrParent ,
' cst_id in (select cst_id from bas_shp where Shp_Kind = 4) ' UNION ALL
SELECT '5' tr_key , '公司总仓库' tr_text , '0000_01_' TrParent ,
' cst_id in (select cst_id from bas_shp where Shp_Kind = 5 ) ' UNION ALL
SELECT '6' tr_key , '分区仓库' tr_text , '0000_01_' TrParent ,
' cst_id in (select cst_id from bas_shp where Shp_Kind = 6 ) ' UNION ALL SELECT '7' tr_key , '特卖场' tr_text , '0000_01_' TrParent , ' cst_id in (select cst_id from bas_shp where Shp_Kind = 7 ) ' UNION ALL SELECT '8' tr_key , '批发客户(代理商)' tr_text , '0000_01_' TrParent , ' cst_id in (select cst_id from bas_shp where Shp_Kind = 8 ) ' UNION ALL SELECT tr_parent+tr_text+'_' tr_key,tr_text,tr_parent,tr_where FROM ( SELECT shp_vis_id3 tr_key , UPPER(LTRIM(RTRIM(ISNULL(shp_vis_name3,'')))) tr_text ,'0001_'+RTRIM(shp_vis_id2)+'_' tr_parent ,' Cst_id in(select Cst_id from bas_shp where shp_id in (SELECT shp_id FROM bas_shp_vis_dtl WHERE shp_vis_id='''+shp_vis_id+''')) ' tr_where FROM bas_shp_vis ) a GROUP BY tr_text,tr_parent,tr_where SELECT distinct * FROM #BasCst ORDER BY tr_key,tr_parent DROP TABLE #BasCst