根据行政区划代码 如何生成所在“省、市、县”的查询

Question

表A为行政区划数据表，如第一列为代码，第二列为行政属地。如652223，伊吾县
表B为另一数据表，其中A列为行政代码。
我想根据表B生成一个查询：根据A列（表B）的行政代码，生成三列（省、市、县）

Answer 1

 
 
查询指定节点及其所有子节点或父节点
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb 
GO
CREATE TABLE tb(id VARCHAR(3),pid VARCHAR(3),[name] VARCHAR(10))
GO
INSERT INTO tb SELECT '001',NULL,'河南省' 
UNION ALL SELECT '002','001','洛阳市'
UNION ALL SELECT '003','001','新乡市' 
UNION ALL SELECT '004','002','栾川县' 
UNION ALL SELECT '005','003','长垣县' 
UNION ALL SELECT '006','002','孟津县' 
UNION ALL SELECT '007','004','冷水乡' 
UNION ALL SELECT '008','004','叫河乡' 
UNION ALL SELECT '009','008','A村' 
UNION ALL SELECT '010','008','B村'
GO
/*
1、查询指定节点及其所有子节点
--如：已知 栾川县
--得到结果id   pid  name
---- ---- ----------
004  002  栾川县
007  004  冷水乡
008  004  叫河乡
009  008  A村
010  008  B村
*/****************************查询开始******************************
--SQL2005 使用CTE
DECLARE @ VARCHAR(10)
SET @='栾川县'
;WITH XiaoAi AS
(
SELECT id FROM tb WHERE [name]=@
UNION ALL
SELECT t.id FROM XiaoAi AS a INNER JOIN tb AS t ON a.id=t.pid
)
SELECT t.* FROM XiaoAi 
--SQL 2000 使用函数
IF OBJECT_ID('dbo.XiaoAi') IS NOT NULL DROP FUNCTION dbo.XiaoAi
GO
CREATE FUNCTION dbo.XiaoAi(@ VARCHAR(20)) 
RETURNS @t TABLE(id VARCHAR(3), pid VARCHAR(3), [name] VARCHAR(20),Level INT)
AS 
BEGIN 
    DECLARE @level INT
    SET @level=1
    INSERT INTO @t SELECT *,@level FROM tb WHERE [name]=@
    WHILE(@@ROWCOUNT>0)
        BEGIN 
            SET @level=@level+1
            INSERT INTO @t SELECT t.*,@level FROM tb AS t,@t AS a WHERE a.id=t.pid AND a.level=@level-1
        END
RETURN 
END
GO
--调用函数
SELECT id, pid,[name]  from dbo.XiaoAi('栾川县') 

Answer 2

表A：
  ID     AreaName
652900  阿克苏地区
652901  阿克苏市
652922  温宿县
652923  库车县
......   ......
表B：
ReportArea      greenName
652901    黄瓜
652922    黄瓜
652923    黄瓜
652901    茄子
652922    茄子
652923    茄子
652901    番茄
652922    番茄
652923    番茄
......            ......
想在程序中生成类似下面的查询结果: 省            市区        县市       greenName 
新疆自治区   阿克苏地区   阿克苏市      黄瓜
新疆自治区   阿克苏地区   温宿县        黄瓜
新疆自治区   阿克苏地区   库车县        黄瓜新疆自治区   阿克苏地区   阿克苏市      茄子
新疆自治区   阿克苏地区   温宿县        茄子
新疆自治区   阿克苏地区   库车县        茄子新疆自治区   阿克苏地区   阿克苏市      番茄
新疆自治区   阿克苏地区   温宿县        番茄
新疆自治区   阿克苏地区   库车县        番茄
......       ......    ......     ......

Answer 3

select province.AreaName , city.AreaName ,town.AreaName , b.greenName 
from b 
inner join a province on province.id=substring(b.ReportArea,1,2)+'0000'
inner join a city on city.id=substring(b.ReportArea,1,4)+'00'
inner join a town on town.ID = b.ReportArea假设id是char，如果不是，需要类型转换
没有sql server， 没有调试。

Answer 4

8楼正解,谢谢afiw及大家。结贴。