日期:2014-05-18 浏览次数:20440 次
--创建环境 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 20