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


    可以通过数据绑定来实现  通过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());
                }
            }
        }
    

      

  • 相关阅读:
    一条select语句的执行流程
    理解数据库的事物,ACID,cap
    java并发volatile和sychnorized的底层机制
    避免死锁的几种方式
    如何减少线程上下文切换
    RestTemplate设置超时时间
    spring事务隔离级别和传播级别
    mysql数据库与其他数据库的区别
    spingcloud组件注解汇总
    python二级选择题易错知识点总结
  • 原文地址:https://www.cnblogs.com/douqiumiao/p/3362955.html
Copyright © 2020-2023  润新知