如何在Classic ASP中用多维结构存储并分组展示数据库数据？

数据库原始数据 #

COMPANY   | CATEGORY   | BRAND    
--------------------------------
Company 1 | Category 2 | Brand A  
Company 1 | Category 2 | Brand B  
Company 1 | Category 2 | Brand C  
Company 1 | Category 1 | Brand X  
Company 1 | Category 1 | Brand Y  
Company 1 | Category 1 | Brand Z  
Company 1 | Category 3 | Brand A  
Company 1 | Category 3 | Brand X  

期望的展示格式 #

COMPANY 1

• CATEGORY 1:

BRAND X
BRAND Y
BRAND Z

• CATEGORY 2

BRAND A
BRAND B
BRAND C

• CATEGORY 3

BRAND A
BRAND X

PHP实现方式 #

PHP中可通过两步实现：先将数据库结果存入多维数组，再循环输出。代码如下：

while ($row = mysql_fetch_assoc($result)) {
    $companies[$row['company']][$row['category']][] = $row['brand'];
}

foreach ($companies AS $company => $categories) {
    echo '<h2>'. htmlentities($company, ENT_COMPAT, 'UTF-8') .'</h2>';
    echo '<ul>';
    foreach ($categories AS $category => $brands) {
        echo '<li>'. htmlentities($category, ENT_COMPAT, 'UTF-8');
        foreach ($brands AS $brand) {
            echo '<br><em>'. htmlentities($brand, ENT_COMPAT, 'UTF-8') .'</em>';
        }
        echo '<br>&nbsp;</li>';
    }
    echo '</ul>';
}

Classic ASP的实现方案 #

在Classic ASP中，最适配的方案是使用字典的字典（Dictionary of Dictionaries），Dictionary对象的键值对映射特性可以完美复刻PHP多维数组的分组逻辑，具体分为两步：

1. 构建分组数据结构 #

从数据库读取数据，逐层嵌套构建字典结构：

<%
' 创建顶级字典：存储「公司名 -> 分类字典」的映射
Set companies = Server.CreateObject("Scripting.Dictionary")

' 假设rs是数据库查询返回的结果集
Do While Not rs.EOF
    Dim companyName, categoryName, brandName
    companyName = rs("COMPANY")
    categoryName = rs("CATEGORY")
    brandName = rs("BRAND")

    ' 当前公司不存在则创建新的分类字典
    If Not companies.Exists(companyName) Then
        Set companies(companyName) = Server.CreateObject("Scripting.Dictionary")
    End If

    Set categories = companies(companyName)
    ' 当前分类不存在则创建新的品牌字典（用字典可自动去重）
    If Not categories.Exists(categoryName) Then
        Set categories(categoryName) = Server.CreateObject("Scripting.Dictionary")
    End If

    Set brands = categories(categoryName)
    ' 将品牌加入分类对应的字典
    If Not brands.Exists(brandName) Then
        brands.Add brandName, brandName
    End If

    rs.MoveNext
Loop
%>

2. 循环输出分组内容 #

遍历字典结构，输出符合要求的HTML格式：

<%
' 遍历所有公司
For Each companyName In companies.Keys
    Response.Write "<p><strong>" & Server.HTMLEncode(companyName) & "</strong></p>" & vbCrLf
    
    Set categories = companies(companyName)
    ' 遍历当前公司的所有分类
    For Each categoryName In categories.Keys
        Response.Write "<ul><li>" & Server.HTMLEncode(categoryName) & "</li></ul>" & vbCrLf
        
        Set brands = categories(categoryName)
        ' 遍历当前分类的所有品牌
        For Each brandName In brands.Keys
            Response.Write "<p>" & Server.HTMLEncode(brandName) & "</p>" & vbCrLf
        Next
        Set brands = Nothing
    Next
    Set categories = Nothing
Next
Set companies = Nothing
%>

补充说明 #

• 若无需品牌去重，可改用ArrayList替代存储品牌的Dictionary，添加品牌时直接调用ArrayList.Add brandName，遍历用For i = 0 To ArrayList.Count - 1即可。
• 使用Server.HTMLEncode处理输出内容，避免XSS风险，作用等同于PHP的htmlentities。

内容的提问来源于stack exchange，提问作者user16761658