SQL Server：如何利用Forms表简化大量Union查询统计表单案例数

现有数据库结构 #

当前的表结构和测试数据如下：

CREATE TABLE Forms ( ID int NOT NULL PRIMARY KEY, FormName varchar );
INSERT INTO Forms (ID, FormName) VALUES (1, 'Password_Reset_Form'), (2, 'Service_Request_Form');

CREATE TABLE Cases ( ID int NOT NULL PRIMARY KEY, CreatedDt datetime );
INSERT INTO Cases (ID, CreatedDt) VALUES (1, '2018-05-8'), (2, '2018-05-9'), (3, '2018-05-10');

CREATE TABLE Password_Reset_Form ( ID int NOT NULL PRIMARY KEY, CaseID int FOREIGN KEY REFERENCES Cases(ID), Subject varchar, FormName varchar FOREIGN KEY REFERENCES Forms(FormName) );
INSERT INTO Password_Reset_Form (ID, CaseID, Subject, FormName) VALUES (1, 1, 'Password Issue', 'Password_Reset_Form');

CREATE TABLE Service_Request_Form ( ID int NOT NULL PRIMARY KEY, CaseID int FOREIGN KEY REFERENCES Cases(ID), Subject varchar, FormName varchar FOREIGN KEY REFERENCES Forms(FormName) );
INSERT INTO Service_Request_Form (ID, CaseID, Subject, FormName) VALUES (1, 2, 'Add User', 'Service_Request_Form'), (1, 3, 'Delete User', 'Service_Request_Form');

当前的痛点 #

现在要统计指定日期范围内各表单的案例数量，需要手动写400多个UNION ALL来关联所有表单表，就像这样：

SELECT t2.FormName, COUNT(1) as TotalCases 
FROM Cases t1 
INNER JOIN ( 
    select CaseID, FormName FROM Password_Reset_Form 
    union all 
    select CaseID, FormName FROM Service_Request_Form 
    /* 400 + more unions */ 
) t2 ON t1.ID = t2.CaseID
WHERE t1.CreatedDt BETWEEN '2018-05-08' AND '2018-05-10' 
GROUP BY t2.FormName

这种方式不仅写起来繁琐，后续新增表单表还要手动修改SQL，维护成本极高。

解决方案：用动态SQL自动生成查询 #

既然Forms表已经存储了所有表单的名称，我们可以通过动态SQL自动拼接所有表单表的查询语句，完全不用手动写那些UNION ALL。下面以SQL Server为例（不同数据库语法略有差异，后面会提适配方式）：

实现代码 #

DECLARE @sql NVARCHAR(MAX)
DECLARE @startDate DATE = '2018-05-08'
DECLARE @endDate DATE = '2018-05-10'

-- 自动生成所有表单表的UNION ALL片段
SELECT @sql = STRING_AGG(
    CONCAT('SELECT CaseID, FormName FROM ', QUOTENAME(FormName)),
    ' UNION ALL '
)
FROM Forms

-- 拼接完整的统计查询语句
SET @sql = CONCAT(
    'SELECT t2.FormName, COUNT(1) as TotalCases 
     FROM Cases t1 
     INNER JOIN (',
    @sql,
    ') t2 ON t1.ID = t2.CaseID
     WHERE t1.CreatedDt BETWEEN ''', @startDate, ''' AND ''', @endDate, '''
     GROUP BY t2.FormName'
)

-- 执行动态生成的SQL
EXEC sp_executesql @sql

关键细节解释 #

1. STRING_AGG函数：这是SQL Server 2017及以上版本的函数，用来把Forms表中的每个FormName转换成对应的SELECT语句，然后用UNION ALL拼接起来。如果是旧版本的SQL Server，可以用FOR XML PATH的方式实现拼接。
2. QUOTENAME函数：用来处理表名中可能包含的特殊字符（比如空格、关键字），避免出现语法错误。
3. 动态参数：把日期参数单独声明，避免直接拼接字符串时的格式问题，也更安全。

其他数据库的适配方式 #

• MySQL：用GROUP_CONCAT代替STRING_AGG，然后用PREPARE和EXECUTE执行动态SQL：

  SET @startDate = '2018-05-08';
  SET @endDate = '2018-05-10';
  
  SELECT GROUP_CONCAT(
      CONCAT('SELECT CaseID, FormName FROM `', FormName, '`')
      SEPARATOR ' UNION ALL '
  ) INTO @sql
  FROM Forms;
  
  SET @sql = CONCAT(
      'SELECT t2.FormName, COUNT(1) as TotalCases 
       FROM Cases t1 
       INNER JOIN (', @sql, ') t2 ON t1.ID = t2.CaseID
       WHERE t1.CreatedDt BETWEEN ''', @startDate, ''' AND ''', @endDate, '''
       GROUP BY t2.FormName'
  );
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  

• Oracle：用LISTAGG拼接字符串，然后用EXECUTE IMMEDIATE执行：

  DECLARE
      sql_stmt VARCHAR2(32767);
      startDate DATE := TO_DATE('2018-05-08', 'YYYY-MM-DD');
      endDate DATE := TO_DATE('2018-05-10', 'YYYY-MM-DD');
  BEGIN
      SELECT LISTAGG(
          'SELECT CaseID, FormName FROM "' || FormName || '"',
          ' UNION ALL '
      ) WITHIN GROUP (ORDER BY FormName)
      INTO sql_stmt
      FROM Forms;
      
      sql_stmt := 'SELECT t2.FormName, COUNT(1) as TotalCases 
                   FROM Cases t1 
                   INNER JOIN (' || sql_stmt || ') t2 ON t1.ID = t2.CaseID
                   WHERE t1.CreatedDt BETWEEN :start AND :end
                   GROUP BY t2.FormName';
      
      EXECUTE IMMEDIATE sql_stmt USING startDate, endDate;
  END;
  /
  

注意事项 #

• 确保执行动态SQL的数据库账号拥有所有表单表的查询权限，否则会报错。
• 因为FormName来自系统维护的Forms表，不是用户输入，所以SQL注入的风险很低；如果FormName可能被外部用户修改，建议额外做校验。
• 后续新增表单表时，只要把新的FormName加入Forms表，这个查询会自动包含新表，完全不用修改SQL。

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