sql Asp.net 生成 小计 合计

 数据如下：（sql 2008 R2版本）

页面显示如下：

sql代码如下：

select [name],[groupName],SUM(total) as totalCount,GROUPING(name),GROUPING(groupName)from testTable group by name,groupName with rollup

 

执行结果如下：

 

 

 

 

 

后台cs文件代码：

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                Bind();
            }
        }   
        public void Bind()
        {
            SqlConnection conn = new SqlConnection("server=HPF-PC;database=aa;uid=sa;pwd=sasa");
            DataTable dt = new DataTable();
            string sql = "select [name],[groupName],SUM(total) as totalCount,GROUPING(name),GROUPING(groupName) from testTable group by name,groupName with  rollup";
            SqlDataAdapter da = new SqlDataAdapter(sql,conn);
            da.Fill(dt);
            this.gvShow.DataSource = dt;
            this.gvShow.DataBind();
            for (int i = 0; i < gvShow.Rows.Count-1; i++)
            {
                if (((Label)(gvShow.Rows[i].FindControl("lblgroup"))).Text=="")
                {
                    gvShow.Rows[i].BackColor = System.Drawing.Color.LightGreen;
                    ((Label)(gvShow.Rows[i].FindControl("lblName"))).Text = "小计：";
                }  
            }
            ((Label)(gvShow.Rows[gvShow.Rows.Count-1].FindControl("lblName"))).Text = "总计：";
            gvShow.Rows[gvShow.Rows.Count - 1].BackColor = System.Drawing.Color.Green;
        }
 

aspx文件代码：

  <asp:GridView runat="server" Width="600px" ID="gvShow" 
            AutoGenerateColumns="false" >
            <Columns>
                <asp:TemplateField HeaderText="姓名">
                    <ItemTemplate>
                        <asp:Label runat="server" ID="lblName" Text='<%#Eval("name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="组名">
                    <ItemTemplate>
                        <asp:Label runat="server" ID="lblgroup" Text='<%#Eval("groupname") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="总计">
                    <ItemTemplate>
                        <asp:Label runat="server" ID="lbltotal" Text='<%#Eval("totalcount") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>