SQL查询动态添加列：活动Delegate场次预订统计方案咨询

针对你遇到的不同活动场次数量不固定、手动添加列效率低下的问题，静态SQL确实没法满足动态列的需求，我们可以通过动态SQL结合条件聚合或者动态PIVOT来实现，下面分两种方案详细说明：

方案一：动态条件聚合（兼容性强，适用于多数数据库） #

条件聚合的核心思路是先抓取目标活动的所有场次，再为每个场次动态生成一个CASE WHEN列，用来标记参会者的预订状态或统计票数，兼容性覆盖MySQL、PostgreSQL、SQL Server等多数主流数据库。

实现步骤： #

1. 提取目标活动下的所有场次名称/ID
2. 动态拼接SQL语句，生成对应场次的统计列
3. 执行拼接后的完整SQL

示例代码（以SQL Server为例，其他数据库可调整语法）： #

DECLARE @EventCode VARCHAR(6) = 'AC2017' -- 可替换为目标活动编码
DECLARE @DynamicColumns NVARCHAR(MAX)
DECLARE @FinalSQL NVARCHAR(MAX)

-- 第一步：获取当前活动的所有场次，拼接成条件聚合列
SELECT @DynamicColumns = STRING_AGG(
    CONCAT('MAX(CASE WHEN s.NAME = ''', QUOTENAME(s.NAME, ''''), ''' THEN ''已预订'' ELSE ''未预订'' END) AS ', QUOTENAME('Session: ' + s.NAME)),
    ', '
)
FROM session s
JOIN delegate_session ds ON s.SESSION_REF = ds.SESSION_REF
JOIN delegate d ON ds.DELEGATE_REF = d.DELEGATE_REF
JOIN event e ON d.EVENT_REF = e.EVENT_REF
WHERE e.code = @EventCode
GROUP BY s.SESSION_REF, s.NAME

-- 第二步：拼接完整查询SQL
SET @FinalSQL = CONCAT('
SELECT 
    e.NAME AS EventName,
    d.code AS DelegateCode,
    d.name AS DelegateName,
    d.MEMBER_REF,
    d.TOTAL_AMOUNT,
    ', @DynamicColumns, '
FROM DELEGATE d
INNER JOIN EVENT e ON d.EVENT_REF = e.EVENT_REF
LEFT JOIN delegate_session ds ON d.DELEGATE_REF = ds.DELEGATE_REF
LEFT JOIN session s ON ds.SESSION_REF = s.SESSION_REF
WHERE d.code > 50 AND d.code < 61 AND e.code = @EventCode
GROUP BY e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT
')

-- 第三步：执行动态SQL（参数化避免注入）
EXEC sp_executesql @FinalSQL, N'@EventCode VARCHAR(6)', @EventCode = @EventCode

如果需要统计预订票数（比如参会者可能订多张同场次票），可以把MAX(CASE...)改成COUNT(CASE...)或SUM(CASE...)，根据实际业务调整。

方案二：动态PIVOT（适用于支持PIVOT的数据库，如SQL Server、Oracle） #

如果你的数据库原生支持PIVOT语法，也可以通过动态获取PIVOT列名来实现，逻辑更贴合“行转列”的业务场景。

示例代码（SQL Server）： #

DECLARE @EventCode VARCHAR(6) = 'AC2017'
DECLARE @PivotColumns NVARCHAR(MAX)
DECLARE @FinalSQL NVARCHAR(MAX)

-- 获取需要PIVOT的场次列名
SELECT @PivotColumns = STRING_AGG(QUOTENAME(s.NAME), ', ')
FROM session s
JOIN delegate_session ds ON s.SESSION_REF = ds.SESSION_REF
JOIN delegate d ON ds.DELEGATE_REF = d.DELEGATE_REF
JOIN event e ON d.EVENT_REF = e.EVENT_REF
WHERE e.code = @EventCode
GROUP BY s.SESSION_REF, s.NAME

-- 拼接PIVOT查询SQL
SET @FinalSQL = CONCAT('
SELECT 
    EventName,
    DelegateCode,
    DelegateName,
    MEMBER_REF,
    TOTAL_AMOUNT,
    ', @PivotColumns, '
FROM (
    SELECT 
        e.NAME AS EventName,
        d.code AS DelegateCode,
        d.name AS DelegateName,
        d.MEMBER_REF,
        d.TOTAL_AMOUNT,
        s.NAME AS SessionName,
        ''已预订'' AS BookingStatus -- 统计票数的话可替换为COUNT(*)或对应数值
    FROM DELEGATE d
    INNER JOIN EVENT e ON d.EVENT_REF = e.EVENT_REF
    LEFT JOIN delegate_session ds ON d.DELEGATE_REF = ds.DELEGATE_REF
    LEFT JOIN session s ON ds.SESSION_REF = s.SESSION_REF
    WHERE d.code > 50 AND d.code < 61 AND e.code = @EventCode
) AS SourceData
PIVOT (
    MAX(BookingStatus) -- 根据需求用MAX/COUNT/SUM
    FOR SessionName IN (', @PivotColumns, ')
) AS PivotTable
')

-- 参数化执行动态SQL
EXEC sp_executesql @FinalSQL, N'@EventCode VARCHAR(6)', @EventCode = @EventCode

关键注意事项 #

1. SQL注入防范：如果活动编码是用户输入的，一定要用参数化查询（如示例中sp_executesql的参数传递），绝对不能直接拼接用户输入的字符串。
2. 空值处理：未预订场次的结果会显示NULL，可以用ISNULL/COALESCE函数替换为'未预订'或0等默认值。
3. 数据库适配：不同数据库的字符串聚合函数不同，比如MySQL用GROUP_CONCAT，PostgreSQL用STRING_AGG，需要根据你的数据库调整对应函数。

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