-- =============================================
---这里是一个存储过程
CREATE PROCEDURE [dbo].[P_Report_GetDailyCoeeByEqptype]
-- Add the parameters for the stored procedure here
@MfgDate nvarchar(128), ---这里是定义生产日期
@EqpType nvarchar(128) ----这里是定义机台的状态
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; ---这里不明白
Set @MfgDate = CONVERT(nvarchar(128),Convert(Datetime,@Mfgdate),111) ---给日期变量赋值
Declare @Coeetarget nvarchar(128) --定义实际产量
Declare @CoeeTargetExcludeIdle nvarchar(128) --定义预期目标 Select @CoeeTarget=COEETarget,@CoeeTargetExcludeIdle=CoeeTargetExcludeIdle from UserDataModel.T_DailyCoeeTarget
where MfgDate = @MfgDate ----这里的一个sql 意思是。根据时间条件,查询coee ,初期和目标值 Select RawData.ResourceID,RawData.ResourceName,RawData.Model,RawData.PCode,Convert (float,RawData.Duration) AS Duration,Pcode.CoeeGroup into #Temp ---这里的Pcode.CoeeGroup into #Temp 不明白什么意思 from
(
(Select ResourceID,ResourceName,Model,PCode,Duration from Dbo.T_ETL_CoeeDailyRawData where MfgDate = @MfgDate And Model = @EqpType) RawData ---这里是根据时间和type 查询所有的信息 并且表命名为RawData
inner join
UserDataModel.T_ST_TesterStatePcode Pcode
on RawData.PCode = Pcode.Pcode ---这里是2表联查。在PCode 相等的情况下
)
Select ResourceID,ResourceName,Model,CoeeGroup,SUM(Duration) As Duration,@Coeetarget As CoeeTargert,@CoeeTargetExcludeIdle As CoeeTargetExcludeIdle From #Temp group by ResourceID,ResourceName,model,CoeeGroup --这里的#Temp 是什么意思
ENDGO
---这里是一个存储过程
CREATE PROCEDURE [dbo].[P_Report_GetDailyCoeeByEqptype]
-- Add the parameters for the stored procedure here
@MfgDate nvarchar(128), ---这里是定义生产日期
@EqpType nvarchar(128) ----这里是定义机台的状态
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; ---这里不明白
Set @MfgDate = CONVERT(nvarchar(128),Convert(Datetime,@Mfgdate),111) ---给日期变量赋值
Declare @Coeetarget nvarchar(128) --定义实际产量
Declare @CoeeTargetExcludeIdle nvarchar(128) --定义预期目标 Select @CoeeTarget=COEETarget,@CoeeTargetExcludeIdle=CoeeTargetExcludeIdle from UserDataModel.T_DailyCoeeTarget
where MfgDate = @MfgDate ----这里的一个sql 意思是。根据时间条件,查询coee ,初期和目标值 Select RawData.ResourceID,RawData.ResourceName,RawData.Model,RawData.PCode,Convert (float,RawData.Duration) AS Duration,Pcode.CoeeGroup into #Temp ---这里的Pcode.CoeeGroup into #Temp 不明白什么意思 from
(
(Select ResourceID,ResourceName,Model,PCode,Duration from Dbo.T_ETL_CoeeDailyRawData where MfgDate = @MfgDate And Model = @EqpType) RawData ---这里是根据时间和type 查询所有的信息 并且表命名为RawData
inner join
UserDataModel.T_ST_TesterStatePcode Pcode
on RawData.PCode = Pcode.Pcode ---这里是2表联查。在PCode 相等的情况下
)
Select ResourceID,ResourceName,Model,CoeeGroup,SUM(Duration) As Duration,@Coeetarget As CoeeTargert,@CoeeTargetExcludeIdle As CoeeTargetExcludeIdle From #Temp group by ResourceID,ResourceName,model,CoeeGroup --这里的#Temp 是什么意思
ENDGO
(
(Select ResourceID,ResourceName,Model,PCode,Duration from Dbo.T_ETL_CoeeDailyRawData where MfgDate = @MfgDate And Model = @EqpType) RawData ---这里是根据时间和type 查询所有的信息 并且表命名为RawData
inner join
UserDataModel.T_ST_TesterStatePcode Pcode
on RawData.PCode = Pcode.Pcode ---这里是2表联查。在PCode 相等的情况下
)
--這是把結果集生成臨時表#Temp Select ResourceID,ResourceName,Model,CoeeGroup,SUM(Duration) As Duration,@Coeetarget As CoeeTargert,@CoeeTargetExcludeIdle As CoeeTargetExcludeIdle From #Temp group by ResourceID,ResourceName,model,CoeeGroup --这里的#Temp 是什么意思--把臨時表當作一個表就行了,把臨時表生成一個新的結果集
SET NOCOUNT { ON | OFF }
备注
当 SET NOCOUNT 为 ON 时,不返回计数。当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。当 SET NOCOUNT 为 ON 时,将不向客户端发送存储过程中每个语句的 DONE_IN_PROC 消息。如果存储过程中包含一些并不返回许多实际数据的语句,网络通信流量便会大量减少,因此,将 SET NOCOUNT 设置为 ON 可显著提高性能。SET NOCOUNT 指定的设置是在执行或运行时生效,而不是在分析时生效。权限
要求具有 public 角色的成员身份。示例
以下示例将禁止显示受影响的行数的消息。 复制代码
USE AdventureWorks;
GO
SET NOCOUNT OFF;
GO
-- Display the count message.
SELECT TOP(5)LastName
FROM Person.Contact
WHERE LastName LIKE 'A%';
GO
-- SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT TOP(5) LastName
FROM Person.Contact
WHERE LastName LIKE 'A%';
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
select Distinct Model as Resourcename,Model as Model, avg(Prod_chargmin) as Prod_chargmin,
AVG(Prod_nonchargemin) as Prod_nonchargemin,AVG(Setup_min) as Setup_min,AVG(Idle_min) as Idle_min,
AVG(Engr_min) as Engr_min,AVG(Pm_min) as Pm_min,AVG(Down_min) Down_min,AVG(Coeetarget) as Coeetarget,
AVG(CoeeTargetExcludeIdle) as CoeeTargetExcludeIdle,AVG(Coee) as Coee,AVG(CoeeExcludeIdle) as CoeeExcludeIdle
from Demo_Report_GetDailyCoeeByEqptype
插入到select dt.Resourcename,dt.Model,dt.Prod_chargmin,dt.Prod_nonchargemin,dt.Setup_min,dt.Idle_min,
dt.Engr_min,dt.Pm_min,dt.Down_min,dt.Coeetarget,dt.CoeeTargetExcludeIdle
,dt.Coee,dt.CoeeExcludeIdle from Demo_Report_GetDailyCoeeByEqptype dt
中 按
select into temp from 来做,怎么写,新手
select into tb(col...)
select col... from tb2
where ....
(
(Select ResourceID,ResourceName,Model,PCode,Duration from Dbo.T_ETL_CoeeDailyRawData where MfgDate = @MfgDate And Model = @EqpType) RawData ---这里是根据时间和type 查询所有的信息 并且表命名为RawData
inner join
UserDataModel.T_ST_TesterStatePcode Pcode
on RawData.PCode = Pcode.Pcode ---这里是2表联查。在PCode 相等的情况下
)
*
into
(select dt.Resourcename,dt.Model,dt.Prod_chargmin,dt.Prod_nonchargemin,dt.Setup_min,dt.Idle_min,
dt.Engr_min,dt.Pm_min,dt.Down_min,dt.Coeetarget,dt.CoeeTargetExcludeIdle
,dt.Coee,dt.CoeeExcludeIdle from Demo_Report_GetDailyCoeeByEqptype dt)t1
from
(select Distinct Model as Resourcename,Model as Model, avg(Prod_chargmin) as Prod_chargmin,
AVG(Prod_nonchargemin) as Prod_nonchargemin,AVG(Setup_min) as Setup_min,AVG(Idle_min) as Idle_min,
AVG(Engr_min) as Engr_min,AVG(Pm_min) as Pm_min,AVG(Down_min) Down_min,AVG(Coeetarget) as Coeetarget,
AVG(CoeeTargetExcludeIdle) as CoeeTargetExcludeIdle,AVG(Coee) as Coee,AVG(CoeeExcludeIdle) as CoeeExcludeIdle
from Demo_Report_GetDailyCoeeByEqptype
)t2
(
Resourcename,
Model,
Prod_chargmin,
Prod_nonchargemin,
Setup_min,
Idle_min,
Engr_min,
Pm_min,
Down_min,
Coeetarget,
CoeeTargetExcludeIdle,
Coee,
CoeeExcludeIdle )
select Distinct
Model as Resourcename,
Model as Model,
AVG(Prod_chargmin) as Prod_chargmin,
AVG(Prod_nonchargemin) as Prod_nonchargemin,
AVG(Setup_min) as Setup_min,
AVG(Idle_min) as Idle_min,
AVG(Engr_min) as Engr_min,
AVG(Pm_min) as Pm_min,
AVG(Down_min) Down_min,
AVG(Coeetarget) as Coeetarget,
AVG(CoeeTargetExcludeIdle) as CoeeTargetExcludeIdle,
AVG(Coee) as Coee,
AVG(CoeeExcludeIdle) as CoeeExcludeIdle
from Demo_Report_GetDailyCoeeByEqptype
group by Model
select Distinct Model as Resourcename,Model as Model, avg(Prod_chargmin) as Prod_chargmin,
AVG(Prod_nonchargemin) as Prod_nonchargemin,AVG(Setup_min) as Setup_min,AVG(Idle_min) as Idle_min,
AVG(Engr_min) as Engr_min,AVG(Pm_min) as Pm_min,AVG(Down_min) Down_min,AVG(Coeetarget) as Coeetarget,
AVG(CoeeTargetExcludeIdle) as CoeeTargetExcludeIdle,AVG(Coee) as Coee,AVG(CoeeExcludeIdle) as CoeeExcludeIdle
from Demo_Report_GetDailyCoeeByEqptype
union
select dt.Resourcename,dt.Model,dt.Prod_chargmin,dt.Prod_nonchargemin,dt.Setup_min,dt.Idle_min,
dt.Engr_min,dt.Pm_min,dt.Down_min,dt.Coeetarget,dt.CoeeTargetExcludeIdle
,dt.Coee,dt.CoeeExcludeIdle from Demo_Report_GetDailyCoeeByEqptype dt
AVG(Prod_nonchargemin) as Prod_nonchargemin,AVG(Setup_min) as Setup_min,AVG(Idle_min) as Idle_min,
AVG(Engr_min) as Engr_min,AVG(Pm_min) as Pm_min,AVG(Down_min) Down_min,AVG(Coeetarget) as Coeetarget,
AVG(CoeeTargetExcludeIdle) as CoeeTargetExcludeIdle,AVG(Coee) as Coee,AVG(CoeeExcludeIdle) as CoeeExcludeIdle
into temp
from Demo_Report_GetDailyCoeeByEqptype
union
select dt.Resourcename,dt.Model,dt.Prod_chargmin,dt.Prod_nonchargemin,dt.Setup_min,dt.Idle_min,
dt.Engr_min,dt.Pm_min,dt.Down_min,dt.Coeetarget,dt.CoeeTargetExcludeIdle
,dt.Coee,dt.CoeeExcludeIdle from Demo_Report_GetDailyCoeeByEqptype dt