62,074
社区成员
发帖
与我相关
我的任务
分享
--创建环境
drop table tt
create table tt(mydate datetime,busNum varchar(10),goodsNum varchar(10),goodsName varchar(10),XF int,TF int,tel varchar(10))
insert into tt select '2007/10/8','K123','aaa111','XO',1000,0,'5849568'
union select '2007/10/8','K123','aaa222','篮球',0,200,'2849598'
union select '2007/10/8','D333','aaa333','自行车',0,200,'5946785'
union select '2007/10/8','D333','aaa555','苹果',0,100,'6584795'
union select '2007/10/8','F555','aaa598','法拉里',100,0,'3359468'
union select '2007/10/8','F555','aaa123','科比战靴',0,2500,'3462159'
--初始数据
select * from tt
日期 车号 货物编号 货物名称 现付 提付 电话
------------------------------------------------------------------------
2007-10-08 00:00:00.000 D333 aaa333 自行车 0 200 5946785
2007-10-08 00:00:00.000 D333 aaa555 苹果 0 100 6584795
2007-10-08 00:00:00.000 F555 aaa123 科比战靴 0 2500 3462159
2007-10-08 00:00:00.000 F555 aaa598 法拉里 100 0 3359468
2007-10-08 00:00:00.000 K123 aaa111 XO 1000 0 5849568
2007-10-08 00:00:00.000 K123 aaa222 篮球 0 200 2849598
--执行SQL语句
select a.mydate,a.busNum,a.goodsNum,a.goodsName,a.XF,a.TF,a.tel from
(
select convert(varchar(10),mydate,111) as mydate,busNum,goodsNum,goodsName,XF,TF,tel,1 as myTemp,busNum as bn from tt
union
select '小计',null,null,null,sum(XF),sum(TF),null,2 as myTemp,max(busNum) as bn from tt group by busNum
union
select '总计',null,null,null,sum(XF),sum(TF),null,3 as myTemp,max(busNum) as bn from tt
) a order by a.bn,a.myTemp
--查询结果
日期 车号 货物编号 货物名称 现付 提付 电话
------------------------------------------------------------------------
2007/10/08 D333 aaa333 自行车 0 200 5946785
2007/10/08 D333 aaa555 苹果 0 100 6584795
小计 NULL NULL NULL 0 300 NULL
2007/10/08 F555 aaa123 科比战靴 0 2500 3462159
2007/10/08 F555 aaa598 法拉里 100 0 3359468
小计 NULL NULL NULL 100 2500 NULL
2007/10/08 K123 aaa111 XO 1000 0 5849568
2007/10/08 K123 aaa222 篮球 0 200 2849598
小计 NULL NULL NULL 1000 200 NULL
总计 NULL NULL NULL 1100 3000 NULL
--如果你不想显示NULL,可以执行以下代码
select a.mydate,isnull(a.busNum,''),isnull(a.goodsNum,''),isnull(a.goodsName,''),a.XF,a.TF,isnull(a.tel,'') from
(
select convert(varchar(10),mydate,111) as mydate,busNum,goodsNum,goodsName,XF,TF,tel,1 as myTemp,busNum as bn from tt
union
select '小计',null,null,null,sum(XF),sum(TF),null,2 as myTemp,max(busNum) as bn from tt group by busNum
union
select '总计',null,null,null,sum(XF),sum(TF),null,3 as myTemp,max(busNum) as bn from tt
) a order by a.bn,a.myTemp
--查询结果
日期 车号 货物编号 货物名称 现付 提付 电话
------------------------------------------------------------------------
2007/10/08 D333 aaa333 自行车 0 200 5946785
2007/10/08 D333 aaa555 苹果 0 100 6584795
小计 0 300
2007/10/08 F555 aaa123 科比战靴 0 2500 3462159
2007/10/08 F555 aaa598 法拉里 100 0 3359468
小计 100 2500
2007/10/08 K123 aaa111 XO 1000 0 5849568
2007/10/08 K123 aaa222 篮球 0 200 2849598
小计 1000 200
总计 1100 3000
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
//……在页脚中显示统计数据……
decimal inout_amount = 0; //进退货单
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
// value += Convert.ToInt64(GridView1.Rows[i].Cells[5].Text);
GridViewRow row = this.GridView1.Rows[i];
inout_amount += Convert.ToDecimal(row.Cells[5].Text.ToString());
//Cells[5]这里被我写死掉了 ..你自己看看在那哪一列哦.....呵呵...
}
e.Row.Cells[5].Text = inout_amount.ToString("0.00"); //进退货单
e.Row.Cells[5].HorizontalAlign = HorizontalAlign.Right;
}
}