如何在ASP.NET MVC中实现列数据求和并在页脚展示

嘿，作为刚接触ASP.NET MVC的新手，要实现页脚展示列总和其实没那么复杂，我来一步步帮你搞定～

第一步：完善SQL查询，同时获取分组数据和总计行 #

你的现有SQL已经在做分组统计了，但还需要补充总计行，这样我们就能直接从数据库拿到分组数据+总计，不用在代码里额外计算。另外注意：GROUP BY必须包含所有非聚合列（也就是SELECT里没用到sum()的字段），不然会报错。

修改后的SQL示例：

select TicketNo, Name, ArrDate, 
       sum(CostumeQuantity) As Quantity, 
       sum(CostumeTotalPrice) As Total, 
       sum(ISNULL(RefundAmount,0)) AS REFUND,
       sum(ISNULL(CostumeTotalPrice,0) - ISNULL(RefundAmount,0)) AS TotalAmount 
from GenerateTicket 
where 1=1 -- 这里保留你的原有条件，比如日期范围之类的
Group by TicketNo, Name, ArrDate -- 补全所有非聚合列
UNION ALL
-- 追加总计行，字段要和上面完全对应
select '总计' as TicketNo, '' as Name, '' as ArrDate,
       sum(CostumeQuantity) As Quantity, 
       sum(CostumeTotalPrice) As Total, 
       sum(ISNULL(RefundAmount,0)) AS REFUND,
       sum(ISNULL(CostumeTotalPrice,0) - ISNULL(RefundAmount,0)) AS TotalAmount 
from GenerateTicket 
where 1=1 -- 这里的WHERE要和上面一致，确保总计是同范围的数据

第二步：控制器里绑定数据到实体列表 #

记得用using自动释放数据库连接，避免资源泄漏，同时把查询结果映射到你的GenerateTicket_Details实体类：

List<GenerateTicket_Details> bk = new List<GenerateTicket_Details>();
// 替换成你的数据库连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["YourConnString"].ConnectionString;

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    string query = @"
        select TicketNo, Name, ArrDate, 
               sum(CostumeQuantity) As Quantity, 
               sum(CostumeTotalPrice) As Total, 
               sum(ISNULL(RefundAmount,0)) AS REFUND,
               sum(ISNULL(CostumeTotalPrice,0) - ISNULL(RefundAmount,0)) AS TotalAmount 
        from GenerateTicket 
        where 1=1
        Group by TicketNo, Name, ArrDate
        UNION ALL
        select '总计' as TicketNo, '' as Name, '' as ArrDate,
               sum(CostumeQuantity) As Quantity, 
               sum(CostumeTotalPrice) As Total, 
               sum(ISNULL(RefundAmount,0)) AS REFUND,
               sum(ISNULL(CostumeTotalPrice,0) - ISNULL(RefundAmount,0)) AS TotalAmount 
        from GenerateTicket 
        where 1=1";

    using (SqlCommand cmd = new SqlCommand(query, con))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                bk.Add(new GenerateTicket_Details
                {
                    TicketNo = reader["TicketNo"].ToString(),
                    Name = reader["Name"].ToString(),
                    ArrDate = reader["ArrDate"].ToString(), // 如果是DateTime类型，改成Convert.ToDateTime(reader["ArrDate"])
                    Quantity = Convert.ToInt32(reader["Quantity"]),
                    Total = Convert.ToDecimal(reader["Total"]),
                    REFUND = Convert.ToDecimal(reader["REFUND"]),
                    TotalAmount = Convert.ToDecimal(reader["TotalAmount"])
                });
            }
        }
    }
}

// 把数据传给视图
return View(bk);

第三步：在Razor视图里展示表格+页脚总计 #

用<tfoot>标签专门放页脚，把最后一行（也就是总计行）的数据高亮显示：

<table class="table table-bordered">
    <thead>
        <tr>
            <th>票号</th>
            <th>姓名</th>
            <th>日期</th>
            <th>数量</th>
            <th>总价</th>
            <th>退款</th>
            <th>实付金额</th>
        </tr>
    </thead>
    <tbody>
        <!-- 遍历除了最后一行（总计）的所有分组数据 -->
        @foreach (var item in Model.Take(Model.Count - 1))
        {
            <tr>
                <td>@item.TicketNo</td>
                <td>@item.Name</td>
                <td>@item.ArrDate</td>
                <td>@item.Quantity</td>
                <td>@item.Total.ToString("C")</td> <!-- 格式化金额为货币格式 -->
                <td>@item.REFUND.ToString("C")</td>
                <td>@item.TotalAmount.ToString("C")</td>
            </tr>
        }
    </tbody>
    <tfoot>
        <!-- 展示总计行，加粗突出 -->
        <tr style="font-weight: bold; background-color: #f5f5f5;">
            <td colspan="3">@Model.Last().TicketNo</td>
            <td>@Model.Last().Quantity</td>
            <td>@Model.Last().Total.ToString("C")</td>
            <td>@Model.Last().REFUND.ToString("C")</td>
            <td>@Model.Last().TotalAmount.ToString("C")</td>
        </tr>
    </tfoot>
</table>

可选方案：不在SQL里加总计，在代码里计算 #

如果你不想修改SQL，也可以先查询分组数据，再用Linq计算总计：

// 先获取分组数据（原SQL去掉UNION ALL部分）
var groupedData = ...; // 你的原有查询逻辑
bk.AddRange(groupedData);

// 计算总计并添加到列表
var totalRow = new GenerateTicket_Details
{
    TicketNo = "总计",
    Quantity = groupedData.Sum(x => x.Quantity),
    Total = groupedData.Sum(x => x.Total),
    REFUND = groupedData.Sum(x => x.REFUND),
    TotalAmount = groupedData.Sum(x => x.TotalAmount)
};
bk.Add(totalRow);

这样视图部分的代码和上面完全一样，同样能实现页脚总计效果～

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