在存储过程中实现:灵活,方便实现功能:加索引序号和总计说一下我的做法,IDENTITY(INT, 1, 1)通过生成索引,和要显示的列表数据存在临时表里,然后在查询结果中union 一个统计信息,你看一下,如果有问题,我可以写个简单例子 -- sp_GetAllUseCar 查找所有车辆使用信息 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllUseCar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_GetAllUseCar] GOCREATE PROCEDURE sp_GetAllUseCarASSELECT IDENTITY(INT, 1, 1) AS WDIndexID, CarUseInfo.DeptID,CarUseInfo.CarUseID, CarUseInfo.UseUID,CarUseInfo.OtherFee, tbUserInfo.nvchUserName as UsenvchUserName, CarUseInfo.Cost,CarUseInfo.Distance, ( isnull(tbDeptInfo.nvchDeptName,'') + isnull(SubDeptInfo.SubDeptName,'')) as nvchDeptName, CarInfo.CarLicense,CarInfo.PricePerKM , CASE CarUseFlag When 1 Then '未审核' When 2 Then '通过' Else '未通过' END as CheckType, CarUseInfo.BeginTime, CarUseInfo.EndTime, CASE IsOut WHEN 1 Then '是' Else '否' End AS IsOutT, CarUseInfo.TargetID, TargetInfo.TargetName, CarUseInfo.OrderCarID, CarUseInfo.CarID, CarUseInfo.UseCarType,TypeCode.TypeName,OutTime,OutDate, CarUseInfo.Fee,CarUseInfo.Distance * CarUseInfo.Fee as SumFee,PassengerAmount,StartPlace, ChauffeurInfo.ChauffeurName,CarUseInfo.ChauSubsidy INTO #tmpFROM CarUseInfo LEFT OUTER JOIN tbDeptInfo ON tbDeptInfo.iDeptId = CarUseInfo.DeptID LEFT OUTER JOIN tbUserInfo ON tbUserInfo.iUserID = CarUseInfo.UseUID LEFT OUTER JOIN CarInfo ON CarInfo.CarID = CarUseInfo.CarID LEFT OUTER JOIN TargetInfo ON TargetInfo.TargetID = CarUseInfo.TargetID LEFT OUTER JOIN TypeCode ON (CarUseInfo.UseCarType = TypeCode.TypeValue) AND TypeCode.TypeItem = 'CarUseType' LEFT JOIN SubDeptInfo ON SubDeptInfo.SubDeptID = CarUseInfo.SubDeptID LEFT OUTER JOIN ChauffeurInfo ON ChauffeurInfo.ChauffeurID = CarUseInfo.ChauffeurID ORder By BeginTime Desc(SELECT * FROM #tmp) union ( SELECT max(WDIndexId) + 1, null,null, null,sum(OtherFee ) , null, sum(Cost),sum(Distance), '合计' as nvchDeptName, null,null , null, null, null,null, null, str(count(*)), null, null,null , null,sum(OutTime) as OutTime,sum(OutDate) as OutDate,sum(Fee) ,sum(Distance *Fee) as SumFee,sum(PassengerAmount) ,'次数', null,null From #tmp) order by WDIndexIdDROP Table #tmp GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
-- sp_GetAllUseCar 查找所有车辆使用信息
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllUseCar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllUseCar]
GOCREATE PROCEDURE sp_GetAllUseCarASSELECT IDENTITY(INT, 1, 1) AS WDIndexID, CarUseInfo.DeptID,CarUseInfo.CarUseID, CarUseInfo.UseUID,CarUseInfo.OtherFee,
tbUserInfo.nvchUserName as UsenvchUserName, CarUseInfo.Cost,CarUseInfo.Distance, ( isnull(tbDeptInfo.nvchDeptName,'') + isnull(SubDeptInfo.SubDeptName,'')) as nvchDeptName,
CarInfo.CarLicense,CarInfo.PricePerKM , CASE CarUseFlag When 1 Then '未审核' When 2 Then '通过' Else '未通过' END as CheckType,
CarUseInfo.BeginTime, CarUseInfo.EndTime, CASE IsOut WHEN 1 Then '是' Else '否' End AS IsOutT,
CarUseInfo.TargetID, TargetInfo.TargetName, CarUseInfo.OrderCarID,
CarUseInfo.CarID, CarUseInfo.UseCarType,TypeCode.TypeName,OutTime,OutDate,
CarUseInfo.Fee,CarUseInfo.Distance * CarUseInfo.Fee as SumFee,PassengerAmount,StartPlace, ChauffeurInfo.ChauffeurName,CarUseInfo.ChauSubsidy
INTO #tmpFROM CarUseInfo LEFT OUTER JOIN tbDeptInfo ON
tbDeptInfo.iDeptId = CarUseInfo.DeptID
LEFT OUTER JOIN tbUserInfo ON
tbUserInfo.iUserID = CarUseInfo.UseUID
LEFT OUTER JOIN CarInfo ON
CarInfo.CarID = CarUseInfo.CarID
LEFT OUTER JOIN TargetInfo ON
TargetInfo.TargetID = CarUseInfo.TargetID
LEFT OUTER JOIN TypeCode ON
(CarUseInfo.UseCarType = TypeCode.TypeValue) AND TypeCode.TypeItem = 'CarUseType'
LEFT JOIN SubDeptInfo ON
SubDeptInfo.SubDeptID = CarUseInfo.SubDeptID
LEFT OUTER JOIN ChauffeurInfo ON
ChauffeurInfo.ChauffeurID = CarUseInfo.ChauffeurID
ORder By BeginTime Desc(SELECT * FROM #tmp) union
(
SELECT max(WDIndexId) + 1, null,null, null,sum(OtherFee ) ,
null, sum(Cost),sum(Distance), '合计' as nvchDeptName,
null,null , null,
null, null,null,
null, str(count(*)), null,
null,null ,
null,sum(OutTime) as OutTime,sum(OutDate) as OutDate,sum(Fee) ,sum(Distance *Fee) as SumFee,sum(PassengerAmount) ,'次数', null,null
From #tmp) order by WDIndexIdDROP Table #tmp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
数据库的数据
bb 100
ww 200
dd 300select username,consume from t_test
union(select '总计', sum(consume) from t_test)
查询结果
//----------------------------------------------
bb 100
ww 200
dd 300
总计 600