求这个表格的写法
求这个报表的写法
上图中有大概的数据表和需要的报表格式 求大神能给出个sql
本人对报表这块不熟悉 希望有大神帮助哈 再次谢过
------解决方案--------------------
------解决方案--------------------
随机选择？到底怎么选？
------解决方案--------------------
上图中有大概的数据表和需要的报表格式 求大神能给出个sql
本人对报表这块不熟悉 希望有大神帮助哈 再次谢过
------解决方案--------------------
ALTER PROCEDURE [dbo].[Proce_Find_OutPutSum_Line_day]
@M01_AreaCode varchar(50),
@M04_WorkSort varchar(50),
@M05_TeamCode varchar(50),
@startyear varchar(50),
@End_year varchar(50),
@M06_StationCode varchar(50),
@P05_PartNumber varchar(50)
AS
BEGIN
if(@M01_AreaCode ='')
begin
set @M01_AreaCode=null
end
if(@M04_WorkSort='')
begin
set @M04_WorkSort=null
end
if(@M05_TeamCode='')
begin
set @M05_TeamCode=null
end
if(@M06_StationCode='')
begin
set @M06_StationCode=null
end
if(@P05_PartNumber='')
begin
set @P05_PartNumber=null
end
select M02_LineCode,sum(P01_Output) as P01_Output,years=year(Currentdate),months=month(Currentdate),days=day(Currentdate)
Into #Output from P01_HourVolume where M01_AreaCode=isnull(@M01_AreaCode,M01_AreaCode)
and M04_WorkSort=isnull(@M04_WorkSort,M04_WorkSort)
and M05_TeamCode=isnull(@M05_TeamCode,M05_TeamCode)
and M06_StationCode=isnull(@M06_StationCode,M06_StationCode)
and P05_PartNumber=isnull(@P05_PartNumber,P05_PartNumber)
and convert(datetime,Currentdate) between convert(datetime,@startyear) and convert(datetime,@End_year)
group by Currentdate,M02_LineCode
declare @sql varchar(max)
set @sql='select Date=(case when years=''10000'' then ''TOTAL'' else ltrim(years)+''-''+ltrim(months)+''-''+ltrim(days) end) '
;with cte as
(
(select top 1000 years,months,M02_LineCode,sum(P01_Output) as P_output,days from #Output group by M02_LineCode,years,months,days order by years,months,days ) union all
(select max(10000) as years,max(10000) months,M02_LineCode,sum(P01_Output) as P_output,max(10000)as days from #Output group by M02_LineCode)
)select *,(select sum(P_output) from cte where years=t.years and months=t.months and days=t.days) as Total Into #cte from cte as t
;with cte1 as
(
select M02_LineCode from #Output group by M02_LineCode
)select @sql = @sql + ' ,sum(case M02_LineCode when ''' + M02_LineCode + ''' then P_output else NULL end) [' + M02_LineCode + ']' from cte1 order by M02_LineCode
select @sql = @sql + ' ,Total from #cte as t group by years,months,days,Total order by years,months,days'
exec (@sql)
drop table #Output
END
------解决方案--------------------
------解决方案--------------------