USE E_MainBoard
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='Usp_QueryingMainTestDataTable')
DROP PROC Usp_QueryingMainTestDataTable
GO
CREATE PROC Usp_QueryingMainTestDataTable
@Line_Body varchar(50)
AS
BEGIN
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='#hours')
drop table #hours
create table #hours(
_hour int
)
declare @num int=0
while(@num<=23)
begin
insert into #hours
values(@num)
set @num=@num+1
end
select P.Job_Number,P.Employee_Name,P.[0],P.[1],P.[2],P.[3],P.[4],P.[5],P.[6],P.[7],P.[8],P.[9],P.[10],P.[11],P.[12],P.[13],P.[14],P.[15],P.[16],P.[17],P.[18],P.[19],P.[20],P.[21],P.[22],P.[23],
isnull(P.[0],0)+isnull(P.[1],0)+isnull(P.[2],0)+isnull(P.[3],0)+isnull(P.[4],0)+isnull(P.[5],0)+isnull(P.[6],0)+isnull(P.[7],0)+isnull(P.[8],0)+isnull(P.[9],0)+isnull(P.[10],0)+isnull(P.[11],0)+
isnull(P.[12],0)+isnull(P.[13],0)+isnull(P.[14],0)+isnull(P.[15],0)+isnull(P.[16],0)+isnull(P.[17],0)+isnull(P.[18],0)+isnull(P.[19],0)+isnull(P.[20],0)+isnull(P.[21],0)+isnull(P.[22],0)+isnull(P.[23],0)
as 'TestTotal' from
(select e.Job_Number,e.Employee_Name,P.[0],p.[1],p.[2],p.[3],p.[4],p.[5],p.[6],p.[7],p.[8],p.[9],p.[10],p.[11],p.[12],p.[13],p.[14],p.[15],p.[16],p.[17],p.[18],p.[19],p.[20],p.[21],p.[22],p.[23] from
(select * from (select _hour,m.JobNumber,count(*)as 'To_Count' from #hours a inner join (
select * from MainBoardData where LineBody=@Line_Body and DateDiff(day,convert(varchar(50),GETDATE(),23),convert(datetime,Date_Time,20))=0)m on(a._hour=DATENAME(hour,CONVERT(datetime,m.Date_Time,20)))
group by m.JobNumber,a._hour)P
PIVOT
(
SUM(To_Count) FOR P._hour IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]))as t)P inner join Employee_Information as e on
P.JobNumber=e.Job_Number)P
drop table #hours
END
GO
执行效果图: