SQL存储过程的问题

MrVN_Pan VNleeman 程序员  2015-10-02 02:58:24
USE [LMGernal]
GO
/****** Object: StoredProcedure [dbo].[LM_DeptTotal] Script Date: 2015/10/2 13:27:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <彭传森>
-- Create date: <2015-09-24>
-- Description: <用于查询部门就餐汇总报表>
-- =============================================
ALTER PROCEDURE [dbo].[LM_DeptTotal]
-- Add the parameters for the stored procedure here
@CompanyID nvarchar(50), --公司ID
@employeenum nvarchar(50), --员工工号
@deptcode nvarchar(50), --部门编码
@startdate nvarchar(50), --开始日期
@enddate nvarchar(50), --结束日期
@machine nvarchar(100), --机器编号
@language nvarchar(10) --语言标识
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select
A.BrushCardID,A.EmployeeID,A.EmployeeNum,A2.DeptID,A2.DeptCode,convert(nvarchar(10),A.BrushDate,120) BrushDate,convert(nvarchar(5),A.BrushTime) BrushTime,A.MachineNum
,dbo.GetLanguangeName('BasicEmployee',A1.EmployeeID,@language) EmployeeName --员工姓名
,dbo.GetLanguangeName('BasicDept',A2.DeptID,@language) DeptName --部门名称
into #tb2
from ConsumeBruchCard A
left join BasicEmployee A1 on A.EmployeeNum=A1.EmployeeNum
left join BasicDept A2 on A1.DeptID=A2.DeptID
where (isnull(@CompanyID,'')='' or A1.CompanyID=@CompanyID)
and (ISNULL(@employeenum,'')='' or A.EmployeeNum in (select * from dbo.fn_splitChars(@employeenum,',')))
and (ISNULL(@deptcode,'')='' or A2.DeptCode in (select * from dbo.fn_splitChars(@deptcode,',')))
and (ISNULL(@machine,'')='' or A.MachineNum in (select * from dbo.fn_splitChars(@machine,',')))
and A.BrushDate between isnull(@startdate,'2001-01-01') and isnull(@enddate,'2099-01-01')
order by DeptName,BrushTime,EmployeeNum


select A.StartTime,A.EndTime,A.Price,isnull(A1.LanguageName,A.CosumeDefaultName) TimeName,A.TimeCode
into #tb1
from ConsumeGroupTime A
left join SystemAllName A1 on A.TimeID=A1.TableLineID and A1.TableCode='ConsumeGroupTime' and A1.SysMLFlag=@language
where A.GroupID in (select GroupID from ConSumeMachine where MachineNum in(select distinct MachineNum from #tb2))


select A.*,convert(decimal(24,2),isnull(A1.Price,0)) BruchPrice,A1.TimeName,A1.TimeCode
into #tb3
from #tb2 A
left join #tb1 A1 on A1.StartTime<=A.BrushTime and A1.EndTime>A.BrushTime

select
DeptName
,sum(case when TimeCode='Breakfast' then BruchPrice else 0 end ) BreakfastMny
,sum(case when TimeCode='Breakfast' then 1 else 0 end ) Breakfastnum
,sum(case when TimeCode='Lunch' then BruchPrice else 0 end ) LunchMny
,sum(case when TimeCode='Lunch' then 1 else 0 end ) Lunchnum
,sum(case when TimeCode='Dinner' then BruchPrice else 0 end ) DinnerMny
,sum(case when TimeCode='Dinner' then 1 else 0 end ) Dinnernum
,sum(BruchPrice) TotalMny
,count(*) Totalnum
from #tb3
group by DeptName


drop table #tb1
drop table #tb2

drop table #tb3

END



这个是写的一段SQL存储过程,然后执行存储过程之后结果如下图,那个总数Totalnum里面那个10好奇怪啊,我要的是每行的Breakfastnum+lunchnum+dinnernum得出的值放到到Totalnum列里面去,为毛我前面三个都是0值他就给我得出一个10来,

...全文
75 1 点赞 打赏 收藏 举报
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
tcmakebest 2015-10-02
这是记录数 count(*), 并不是楼主说的字段值相加.
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-10-02 02:58
社区公告
暂无公告