如何设计数据表存储多用户当月数据并实现转置展示（ASPX站点）

一、SQL层面解决转置与去重问题 #

核心思路是用动态PIVOT将日期列转置为表头，同时通过CROSS JOIN生成所有用户-日期组合，配合LEFT JOIN判断用户是否存在当日记录，最终确保每个用户仅占一行。

假设你的表结构如下：

• USER_TABLE：UserId（主键）、UserName
• DATE_TABLE：DateId（主键）、RecordDate（日期字段）
• CONNECTION_TABLE：ConnId（主键）、UserId、DateId

动态PIVOT查询代码 #

-- 1. 生成所有日期列名（格式化为YYYY-MM-DD，用方括号包裹避免关键字冲突）
DECLARE @DateColumns NVARCHAR(MAX)
SELECT @DateColumns = STRING_AGG(QUOTENAME(CONVERT(VARCHAR(10), RecordDate, 23)), ',')
FROM DATE_TABLE

-- 2. 构建动态转置查询
DECLARE @PivotQuery NVARCHAR(MAX)
SET @PivotQuery = N'
SELECT UserName, ' + @DateColumns + '
FROM (
    SELECT 
        u.UserName,
        CONVERT(VARCHAR(10), d.RecordDate, 23) AS RecordDate,
        -- 存在连接记录则显示P，否则显示NP
        CASE WHEN c.ConnId IS NOT NULL THEN ''P'' ELSE ''NP'' END AS PresenceStatus
    FROM USER_TABLE u
    -- 生成所有用户与日期的笛卡尔积，确保每个用户每个日期都有记录
    CROSS JOIN DATE_TABLE d
    -- 左连接关联连接表，判断是否存在当日记录
    LEFT JOIN CONNECTION_TABLE c ON u.UserId = c.UserId AND d.DateId = c.DateId
) AS SourceTable
PIVOT (
    -- 用MAX聚合确保每个用户-日期仅返回一个值，避免重复行
    MAX(PresenceStatus)
    FOR RecordDate IN (' + @DateColumns + ')
) AS PivotTable
'

-- 执行动态查询
EXEC sp_executesql @PivotQuery

如果日期是固定的，也可以用静态PIVOT（示例日期为2024-01-01、2024-01-02）：

SELECT UserName, [2024-01-01], [2024-01-02]
FROM (
    SELECT 
        u.UserName,
        CONVERT(VARCHAR(10), d.RecordDate, 23) AS RecordDate,
        CASE WHEN c.ConnId IS NOT NULL THEN 'P' ELSE 'NP' END AS PresenceStatus
    FROM USER_TABLE u
    CROSS JOIN DATE_TABLE d
    LEFT JOIN CONNECTION_TABLE c ON u.UserId = c.UserId AND d.DateId = c.DateId
) AS SourceTable
PIVOT (
    MAX(PresenceStatus)
    FOR RecordDate IN ([2024-01-01], [2024-01-02])
) AS PivotTable

二、ASPX页面绑定展示 #

用GridView控件可以快速实现表格展示，支持自动生成动态列，无需手动定义表头：

ASPX页面代码 #

<asp:GridView ID="gvUserPresence" runat="server" 
    AutoGenerateColumns="true"
    CssClass="table table-bordered"
    HeaderStyle-BackColor="#f5f5f5"
    RowStyle-HorizontalAlign="Center">
</asp:GridView>

后台C#绑定代码 #

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindPresenceData();
    }
}

private void BindPresenceData()
{
    // 替换为你的数据库连接字符串
    string connStr = ConfigurationManager.ConnectionStrings["YourDBConn"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connStr))
    {
        // 推荐将动态SQL封装为存储过程，这里直接调用存储过程示例
        SqlCommand cmd = new SqlCommand("GetUserPresencePivot", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        
        gvUserPresence.DataSource = dt;
        gvUserPresence.DataBind();
    }
}

注：如果不想用存储过程，也可以直接在后台拼接动态SQL字符串执行，但存储过程更易维护和安全。

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