SQL存储过程的问题
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来,

...全文
相关推荐
SQL中存储过程中SQL语句的单引号和双引号问题[总结].pdf
SQL中存储过程中SQL语句的单引号和双引号问题[总结].pdf
解决SQL下数据库修改删除用户未能找到存储过程问题
解决SQL下数据库修改删除用户未能找到存储过程问题。 此方法已经经过测试并得到了有效的运用。
ASP技术常遇问题解答-如何调用SQL Server的存储过程?.zip
ASP技术问题解答,希望对你有帮助。
pl sql批量执行多个sql文件和存储过程
pl sql批量执行多个sql文件和存储过程,需要pl/sql开发方面资料的可以找我
SQL 存储过程发送HTTP请求
在网上找到的,能在SQL的存储过程中发送HTTP请求的SQL语句,个人觉得还不错,很好用,拿来分享下。


疑难问题
