--基础表1(这样的一个树形结构)：TabZL
kjnd fzdm fzmc
2013 201 mc201
2013 20101 mc20101
2013 2010101 mc2010101
2013 20102 mc20102
2013 2010201 mc2010201
2013 2010202 mc2010202
2013 202 mc202
...
2013 301 mc301
2013 30101 mc30101
2013 30102 mc30102
2013 302 mc302
2013 30201 mc30201
2013 30202 mc30202
...
--业务表TabSJ
kjnd kmdm fzdm4 fzdm5
2013 A001 2010101 30101
2013 A001 2010101 30102
2013 A001 2010102 30101
2013 A001 2010101 30202
...
--希望得到结果表(fzdm4,fzmc4,fzdm5,fzmc5实际上都是基础表TabZL里的，2开头为FZDM4，3开头为
--FZDM5，SFMX：是否明细，0代表：否；1代表：是)：
kjnd fzdm4 fzdm4mc fzdm5 fzdm5mc sfmx
2013 201 mc201 0
2013 20101 mc20101 0
2013 2010101 mc2010101 301 mc301 0
2013 2010101 mc2010101 30101 mc30101 1
2013 2010101 mc2010101 30102 mc30102 1
2013 2010101 mc2010101 302 mc302 0
2013 2010101 mc2010101 30202 mc30202 1
2013 2010101 mc2010102 301 mc301 0
2013 2010101 mc2010102 30101 mc30101 1
...
------解决方案--------------------
--> 测试数据：#TabZL
IF OBJECT_ID('tempdb.dbo.#TabZL') IS NOT NULL DROP TABLE #TabZL
GO
CREATE TABLE #TabZL([kjnd] INT,[fzdm] INT,[fzmc] VARCHAR(9))
INSERT #TabZL
SELECT 2013,201,'mc201' UNION ALL
SELECT 2013,20101,'mc20101' UNION ALL
SELECT 2013,2010101,'mc2010101' UNION ALL
SELECT 2013,20102,'mc20102' UNION ALL
SELECT 2013,2010201,'mc2010201' UNION ALL
SELECT 2013,2010202,'mc2010202' UNION ALL
SELECT 2013,202,'mc202' UNION ALL
SELECT 2013,301,'mc301' UNION ALL
SELECT 2013,30101,'mc30101' UNION ALL
SELECT 2013,30102,'mc30102' UNION ALL
SELECT 2013,302,'mc302' UNION ALL
SELECT 2013,30201,'mc30201' UNION ALL
SELECT 2013,30202,'mc30202'
--> 测试数据：#TabSJ
IF OBJECT_ID('tempdb.dbo.#TabSJ') IS NOT NULL DROP TABLE #TabSJ
GO
CREATE TABLE #TabSJ([kjnd] INT,[kmdm] VARCHAR(4),[fzdm4] INT,[fzdm5] INT)
INSERT #TabSJ
SELECT 2013,'A001',2010101,30101 UNION ALL
SELECT 2013,'A001',2010101,30102 UNION ALL
SELECT 2013,'A001',2010102,30101 UNION ALL
SELECT 2013,'A001',2010101,30202
--------------开始查询--------------------------
SELECT *,sfmx=CASE WHEN c.[fzmc] IS NULL THEN 0 ELSE 1 END FROM