实现对数据进行分组小计并计算合计的实例asp.net

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

可以通过数据绑定来实现通过union all 来实现数据库

SELECT * FROM v3_pay_list2 where ( (ought_date >= '2012-12-06') and (ought_date <'2013/9/16 0:00:00') and (corp_id = '0001'))

union all select 'XXXXXXXXXX', corp_id,dep_id,dep_name,vendor_id,'','', sum(amt),'','',vendor_pay,vendor_approval from (select * from v3_pay_list where ( (ought_date >= '2012-12-06') and (ought_date <'2013/9/16 0:00:00') and (corp_id = '0001')) ) u1 group by corp_id,dep_id,dep_name,vendor_id,vendor_pay,vendor_approval

union all select 'YYYYYYYYYY', corp_id,dep_id,dep_name,'Z','','',sum(amt),'','',vendor_pay,vendor_approval from (select * from v3_pay_list where ( (ought_date >= '2012-12-06') and (ought_date <'2013/9/16 0:00:00') and (corp_id = '0001')) ) u2 group by corp_id,dep_id,dep_name,vendor_pay,vendor_approval

union all select '','','ZZZZZZZZZZ','','','','',sum(amt),'','',max(vendor_pay),max(vendor_approval) from (select * from v3_pay_list where ( (ought_date >= '2012-12-06') and (ought_date <'2013/9/16 0:00:00') and (corp_id = '0001'))) u3 having sum(amt) is not null order by dep_id ,vendor_id ,compare_list_id

查出的结果类似于：

这样的结果集然后在Gridview OnRowDataBound 事件中进行绑定修改：

 protected void dgResult_ItemDataBound(object sender, DataGridItemEventArgs e)
    {
        //设置datagrid的颜色
        e.Item.BackColor = CommonTools.getDGRowColor(e.Item.ItemIndex + 1);

        //设置小计和合计
        DataRowView drv = (DataRowView)e.Item.DataItem;
        if (drv != null)
        {
            if (drv["compare_list_id"].ToString().Trim().Equals("XXXXXXXXXX"))
            {
                e.Item.BackColor = CommonTools.SubRowColor;
                e.Item.Cells[0].Text = "小计：";
                e.Item.Cells[0].ColumnSpan = 5;
                for (int i = 1; i < 5; i++)
                    e.Item.Cells.RemoveAt(1);
                e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
                e.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;
                e.Item.Cells[2].Text = " ";
                e.Item.Cells[3].Text = " ";
            }
            else if (drv["compare_list_id"].ToString().Trim().Equals("YYYYYYYYYY"))
            {
                e.Item.BackColor = CommonTools.TotalRowColor;
                e.Item.Cells[0].Text = "合计：";
                e.Item.Cells[0].ColumnSpan = 5;
                for (int i = 1; i < 5; i++)
                    e.Item.Cells.RemoveAt(1);
                e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
                e.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;
                e.Item.Cells[2].Text = " ";
                e.Item.Cells[3].Text = " ";
            }
            else if (drv["dep_id"].ToString().Trim().Equals("ZZZZZZZZZZ"))
            {
                e.Item.BackColor = CommonTools.TotalRowColor;
                e.Item.Cells[0].Text = "总计：";
                e.Item.Cells[0].ColumnSpan = 5;
                for (int i = 1; i < 5; i++)
                    e.Item.Cells.RemoveAt(1);
                e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
                e.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;
                e.Item.Cells[2].Text = " ";
                e.Item.Cells[3].Text = " ";
            }
            else
            {
                e.Item.Cells[1].Text = CommonTools.TrimZeroLeft(e.Item.Cells[1].Text.Trim());
            }
        }
    }