通过地区表生成地区全称

李工

2个月前更新

100

上图D列是通过Excel生成的全称结果，具体过程看下文AI描述。

本文目录 隐藏

在WorkFine平台内的应用

在WorkFine内提供了地区表的公共数据，有时候我们需要通过地区编号查找到区域名称，这时候就需要知道该编号对应的全称，例如：编号为110107，就需要知道全称北京北京市石景山区这样的结果，而不是单一的区域名称。

应用实例：

由身份证号查询到所属地区，身份证号前6位为地区编码【更多身份证知识】

AI给的实现过程

Excel表格法

我明白了，让我们尝试一个更通用的方法来构建区域的全名，考虑到可能存在多级嵌套的情况。在这种情况下，可以使用Excel的公式递归地构建全名。

假设您的数据如下：

A	B	C
110000	北京
110100	北京市	110000
110101	东城区	110100
110102	西城区	110100

您可以在D列（例如D2单元格）使用以下公式来构建全名：

=IF(C2="", B2, IFERROR(INDEX($D$2:$D$5, MATCH(C2, $A$2:$A$5, 0))&" "&B2, B2))

这里的公式解释如下：

IF(C2="", B2, ... )：如果C2单元格为空，这意味着这是一个顶级区域，直接返回B2单元格的值。
IFERROR(..., B2)：如果在查找过程中出现错误（例如找不到匹配项），则返回B2单元格的值。
INDEX($D$2:$D$5, MATCH(C2, $A$2:$A$5, 0))：查找C2单元格的值在A列的位置，并返回相同位置上的D列的值（即上级区域的全名）。
" "&B2：将找到的上级区域的全名与当前区域名称拼接。

此公式需要从第二行开始应用，并且需要确保范围 $D$2:$D$5 覆盖所有可能的上级区域。

请注意，这个公式适用于Excel 2007及以上版本。如果你使用的是较旧的版本，或者需要处理非常大的数据集，这种方法可能会变得不太高效。对于更复杂的层级结构，你可能需要考虑使用VBA宏或者其他编程语言来处理。

SQL Server递归法

在SQL Server中处理这种层次结构的问题通常可以通过递归公共表表达式 (Recursive Common Table Expressions, CTE) 来实现。递归CTE允许你在SQL查询中定义一个递归关系，从而能够处理具有层级结构的数据。

假设你的表名为 Regions，并且表结构如下：

CREATE TABLE Regions (
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    ParentID INT NULL,
    CONSTRAINT FK_Regions_Parent FOREIGN KEY (ParentID) REFERENCES Regions(ID)
);

其中 ID 是区域的唯一标识符，Name 是区域名称，ParentID 是上级区域的ID。

你可以使用递归CTE来构建每个区域的全名，如下所示：

WITH RecursiveRegions AS (
    -- 锚成员: 处理顶层节点
    SELECT 
        ID, 
        Name AS RegionName, 
        ParentID, 
        Name AS FullName
    FROM 
        Regions
    WHERE 
        ParentID IS NULL
    UNION ALL
    -- 递归成员: 处理子节点
    SELECT 
        r.ID, 
        r.Name, 
        r.ParentID, 
        rr.FullName + ' ' + r.Name AS FullName
    FROM 
        Regions r
    INNER JOIN 
        RecursiveRegions rr ON r.ParentID = rr.ID
)
SELECT 
    ID, 
    Name, 
    ParentID, 
    FullName
FROM 
    RecursiveRegions;

在这个递归CTE中：

锚成员 (Anchor Member)：选择所有没有父节点的记录，并将其名称作为全名。
递归成员 (Recursive Member)：对每个有父节点的记录，通过JOIN操作与递归产生的结果集合相连接，并将父节点的全名与当前节点的名称拼接成新的全名。

最终，这个递归CTE会遍历所有的父节点直到达到顶层节点，为每个区域生成一个完整的名称。

请注意，为了使递归CTE正常工作，你需要确保 MAXRECURSION 设置得足够大以覆盖所有的层级，或者设置为0来禁用递归限制。但是，你应该谨慎使用 MAXRECURSION 0，因为它可能会导致无限递归。

-- 设置递归限制
SET MAXRECURSION 0; -- 或者适当的数值，比如100

这样，你就可以得到一个包含所有区域及其全名的结果集。

应用实例

评分

欢迎为Ta评分