疑难问题
看看这代码,脑壳痛!
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--查询前两年的-五年之内的
-- =============================================
ALTER PROCEDURE [dbo].[proce_Find_GetBienniaMFG_cost]
--传入参数
@year int,
--上一年的
@toOneavgvalue DECIMAL(10, 1) output,
--当前年的
@nonceAvgvalue DECIMAL(10, 1) output
AS
BEGIN
select @toOneavgvalue= CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)) from dbo.MFG_cost where Vintage=@year-1 and [Firm-empty]='Budget'
select @nonceAvgvalue=CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)) from dbo.MFG_cost where Vintage=@year and [Firm-empty]='Budget'
select * into #SumMFG_cost from
(
select
colname = LTRIM(b.Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year-2 Vintage,'Actual' [Firm-empty] union all
select @year-3,'Actual' )b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.[Firm-empty]=b.[Firm-empty]
group by b.Vintage
UNION ALL
--上一年每季的
select
colname = LTRIM(b.Quarter) + N' '+LTRIM(b.Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year-1 Vintage ,'Q1' Quarter,'Actual' [Firm-empty] union all
select @year-1 ,'Q2','Actual' union all
select @year-1 ,'Q3','Actual' union all
select @year-1 ,'Q4','Actual' )b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.Quarter=b.Quarter and a.[Firm-empty]=b.[Firm-empty]
group by b.Vintage,b.Quarter
UNION ALL
--上一年的
select
colname = LTRIM(Vintage) + N'',
[value] =CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from MFG_cost
where [Vintage]=@year-1 and [Firm-empty]='Actual'
group by Vintage
UNION ALL
--本年度每月的
select
colname = LTRIM(b.Months) + N'',
[value] =CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year Vintage ,'January' Months,'Actual' [Firm-empty] union all
select @year ,'February','Actual' union all
select @year ,'March','Actual' union all
select @year ,'April','Actual' union all
select @year ,'May','Actual' union all
select @year ,'June','Actual' union all
select @year ,'July','Actual' union all
select @year ,'August','Actual' union all
select @year ,'September','Actual' union all
select @year ,'October','Actual' union all
select @year ,'November','Actual' union all
select @year ,'December','Actual')b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.Months=b.Months and a.[Firm-empty]=b.[Firm-empty]
GROUP BY b.Vintage,b.Months
UNION ALL
--本年度每季的
select
colname = LTRIM(b.Quarter) + N' '+LTRIM(b.Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year Vintage ,'Q1' Quarter,'Actual' [Firm-empty] union all
select @year ,'Q2','Actual'union all
select @year ,'Q3','Actual' union all
select @year ,'Q4','Actual' )b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.Quarter=b.Quarter and a.[Firm-empty]=b.[Firm-empty]
group by b.Vintage,b.Quarter
UNION ALL
--今年的
select
colname = LTRIM(Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from MFG_cost
where [Vintage]=@year and [Firm-empty]='Actual'
group by Vintage
)t
select * from #SumMFG_cost
-- declare @sql varchar(max)
-- select @sql = isnull(@sql + '],[' , '') + colname from #SumMFG_cost group by colname
-- set @sql = '[' + @sql + ']'
-- exec ('select * from (select * from #SumMFG_cost) a pivot (max([value]) for colname in (' + @sql + ')) b')
END
colname value Color
2008 NULL NULL
2009 20.0 NULL
Q1 2010 13.3 NULL
Q2 2010 13.3 NULL
Q3 2010 13.3 NULL
Q4 2010 13.3 NULL
2010 13.3 NULL
January 10.0 NULL
February 10.0 NULL
March 10.0 NULL
April 10.0 NULL
May 10.0 NULL
June 10.0 NULL
July 10.0 NULL
August 10.0 NULL
September 10.0 NULL
October NULL NULL
November NULL NULL
December NULL NULL
Q1 2011 10.0 NULL
Q2 2011 10.0 NULL
Q3 2011 10.0 NULL
Q4 2011 NULL NULL
2011 10.0 NULL
为什么打印出的结果Color为null,
看看我case 是不是判断有总是!
...全文
相关推荐
BET去脑壳算法 欢迎下载
### 一、BET去脑壳算法概述BET去脑壳算法是一种用于分割MRI头像图像中的大脑与非大脑区域的自动化方法。
[山体公园]深圳大脑壳山公园国际竞赛方案连接并生长
深圳大脑壳山公园国际竞赛方案连接并生长以下是对标题、描述、标签和部分内容的详细解释：**景观设计**大脑壳山公园国际竞赛方案连接并生长是关于深圳大脑壳山公园的景观设计项目。景观设计是城市规
成长之痛.doc
白岩松的名言“痛并快乐着”被用来诠释这种“成长痛”的本质。这种痛苦，虽然让人难以忍受，但它却伴随着个人和企业进步的喜悦。
那脑壳里的斧凿声
**自我对话与反思**：最后的斧凿声源自自己的脑壳，意味着所有的改变和挑战都源于内心的决定。这种自我审视和自我激励的过程，是个人成长的关键。
走路脚跟痛,当心“跟痛症”
脚跟痛的主要病因可以归结为“跟痛症”，该症是一系列不同病因的病症的共同表现形式，下面将详细介绍跟痛症的常见病因和病理。首先，跖筋膜炎是跟痛症中最常见的病因之一。
