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