基于日期范围用SQL Server和PHP实现双表透视查询

直接上可落地的代码和关键修复点，解决你加日期范围、关联表2、计算总计/百分比时的报错问题。

1. 核心SQL实现（静态月份，适合固定统计周期） #

假设你要统计2024年1-6月的数据，静态透视的SQL如下：

WITH monthly_data AS (
    SELECT 
        accountname,
        -- 提取月份作为透视列名，格式如'Jan_2024'
        FORMAT(dateposted, 'MMM_yyyy') AS month_col,
        amount
    FROM 表1
    -- 日期范围过滤，替换成你的实际区间
    WHERE dateposted BETWEEN '2024-01-01' AND '2024-06-30'
),
pivoted_data AS (
    SELECT 
        accountname,
        -- 空值转0，避免总计计算错误
        ISNULL([Jan_2024], 0) AS Jan_2024,
        ISNULL([Feb_2024], 0) AS Feb_2024,
        ISNULL([Mar_2024], 0) AS Mar_2024,
        ISNULL([Apr_2024], 0) AS Apr_2024,
        ISNULL([May_2024], 0) AS May_2024,
        ISNULL([Jun_2024], 0) AS Jun_2024,
        -- 直接累加月度列得到总金额
        ISNULL([Jan_2024],0)+ISNULL([Feb_2024],0)+ISNULL([Mar_2024],0)+ISNULL([Apr_2024],0)+ISNULL([May_2024],0)+ISNULL([Jun_2024],0) AS total_amount
    FROM monthly_data
    PIVOT (
        SUM(amount)
        FOR month_col IN ([Jan_2024], [Feb_2024], [Mar_2024], [Apr_2024], [May_2024], [Jun_2024])
    ) AS p
)
-- 关联表2并计算完成率
SELECT 
    pd.accountname,
    pd.Jan_2024,
    pd.Feb_2024,
    pd.Mar_2024,
    pd.Apr_2024,
    pd.May_2024,
    pd.Jun_2024,
    pd.total_amount,
    ISNULL(t.target, 0) AS target,
    -- 处理target为0的情况，避免除以0报错
    CASE 
        WHEN ISNULL(t.target, 0) = 0 THEN 0
        ELSE ROUND((pd.total_amount / t.target) * 100, 2)
    END AS percentage
FROM pivoted_data pd
-- 左连接保证表1所有账号都能显示，哪怕表2无对应target
LEFT JOIN 表2 t ON pd.accountname = t.accountname
ORDER BY pd.accountname;

关键修复点（对应你可能踩的坑）： #

• 日期范围位置：把过滤放在CTE最外层，避免透视时混入无关数据
• 空值处理：用ISNULL将空月度金额转0，否则总计会漏算
• 跨表关联：用LEFT JOIN而非INNER JOIN，防止丢失无target的账号数据
• 除以0防护：用CASE判断target是否为0，避免SQL运行报错
• 总计计算：在透视后累加月度列，比提前聚合更准确

2. 动态月份SQL（适配任意时间范围） #

如果需要自动识别时间范围内的所有月份，用动态SQL生成透视列：

DECLARE @start_date DATE = '2024-01-01';
DECLARE @end_date DATE = '2024-06-30';
DECLARE @month_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成所有需要透视的月份列名
SELECT @month_cols = STRING_AGG(QUOTENAME(FORMAT(DATEADD(month, number, @start_date), 'MMM_yyyy')), ', ')
FROM master.dbo.spt_values
WHERE type = 'P'
AND DATEADD(month, number, @start_date) <= @end_date;

-- 拼接完整SQL
SET @sql = N'
WITH monthly_data AS (
    SELECT 
        accountname,
        FORMAT(dateposted, ''MMM_yyyy'') AS month_col,
        amount
    FROM 表1
    WHERE dateposted BETWEEN ''' + CONVERT(NVARCHAR, @start_date, 23) + ''' AND ''' + CONVERT(NVARCHAR, @end_date, 23) + '''
),
pivoted_data AS (
    SELECT 
        accountname,
        ' + @month_cols + ',
        -- 动态累加月度列计算总计
        ' + STRING_AGG('ISNULL(' + QUOTENAME(FORMAT(DATEADD(month, number, @start_date), 'MMM_yyyy')) + ', 0)', '+') + ' AS total_amount
    FROM monthly_data
    PIVOT (
        SUM(amount)
        FOR month_col IN (' + @month_cols + ')
    ) AS p
)
SELECT 
    pd.accountname,
    ' + @month_cols + ',
    pd.total_amount,
    ISNULL(t.target, 0) AS target,
    CASE 
        WHEN ISNULL(t.target, 0) = 0 THEN 0
        ELSE ROUND((pd.total_amount / t.target) * 100, 2)
    END AS percentage
FROM pivoted_data pd
LEFT JOIN 表2 t ON pd.accountname = t.accountname
ORDER BY pd.accountname;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

3. PHP调用示例（PDO方式） #

用PDO连接SQL Server，执行查询并输出HTML表格：

<?php
// 数据库连接参数
$serverName = "你的服务器名";
$database = "你的数据库名";
$username = "你的用户名";
$password = "你的密码";

try {
    // 建立连接
    $conn = new PDO("sqlsrv:Server=$serverName;Database=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 复制上面的静态SQL到此处
    $sql = "WITH monthly_data AS (...) -- 完整静态SQL内容";
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // 获取结果集
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 输出HTML表格
    echo '<table border="1">';
    // 输出表头
    echo '<tr>';
    foreach(array_keys($result[0]) as $col) {
        echo '<th>' . htmlspecialchars($col) . '</th>';
    }
    echo '</tr>';
    // 输出数据行
    foreach($result as $row) {
        echo '<tr>';
        foreach($row as $value) {
            echo '<td>' . htmlspecialchars($value) . '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';

} catch(PDOException $e) {
    die("数据库错误: " . $e->getMessage());
}

$conn = null;
?>

常见错误排查： #

1. 日期格式问题：确保dateposted是DATE/DATETIME类型，过滤时用'YYYY-MM-DD'标准格式
2. 账号匹配问题：检查两张表的accountname是否有大小写/空格差异，可加LTRIM(RTRIM(accountname))统一
3. 动态SQL语法错误：用PRINT @sql输出拼接后的SQL，直接在SSMS中运行排查
4. 总计计算错误：必须在透视后累加月度列，提前聚合会导致重复计算

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