CREATE PROCEDURE 存储过程名称
@CODE VARCHAR(50)--代码
,@DAYCOUNT INT--天数
AS
BEGIN
DECLARE @COUNT INT
SELECT @COUNT=SUM(1)FROM 表名
IF @COUNT<@DAYCOUNT
BEGIN
SELECT *,NULL [日均价] FROM 表名 WHERE 代码=@CODE
RETURN
END
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY 时间)RN,* FROM 表名 WHERE 代码=@CODE
)
SELECT T1.RN,T1.代码,T1.成交额,T1.成交量,T1.时间,SUM(T2.成交额)/SUM(T2.成交量)[日均价]
FROM CTE T1
LEFT JOIN CTE T2 ON T1.RN>=T2.RN AND T1.RN-T2.RN<@DAYCOUNT
GROUP BY T1.RN,T1.代码,T1.成交额,T1.成交量,T1.时间
END你参考下,根据需要自己修改
@CODE VARCHAR(50)--代码
,@DAYCOUNT INT--天数
AS
BEGIN
DECLARE @COUNT INT
SELECT @COUNT=SUM(1)FROM 表名
IF @COUNT<@DAYCOUNT
BEGIN
SELECT *,NULL [日均价] FROM 表名 WHERE 代码=@CODE
RETURN
END
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY 时间)RN,* FROM 表名 WHERE 代码=@CODE
)
SELECT T1.RN,T1.代码,T1.成交额,T1.成交量,T1.时间,SUM(T2.成交额)/SUM(T2.成交量)[日均价]
FROM CTE T1
LEFT JOIN CTE T2 ON T1.RN>=T2.RN AND T1.RN-T2.RN<@DAYCOUNT
GROUP BY T1.RN,T1.代码,T1.成交额,T1.成交量,T1.时间
END你参考下,根据需要自己修改
LEFT JOIN CTE T2 ON T1.RN>=T2.RN AND T1.RN-T2.RN<@DAYCOUNT 这段是怎么个意思呢? 另外我想update 表名 里面的 日均价 怎么update呢
@CODE VARCHAR(50)--代码
,@DAYCOUNT INT--天数
AS
BEGIN
DECLARE @COUNT INT
SELECT @COUNT=SUM(1)FROM 表名 WHERE 代码=@CODE
IF @COUNT<@DAYCOUNT
BEGIN
RETURN
END
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY 时间)RN,* FROM 表名 WHERE 代码=@CODE
)
UPDATE A
SET [日均价]=T.[日均价1]
FROM
CTE A JOIN
(SELECT T1.RN,SUM(T2.成交额)/SUM(T2.成交量)[日均价1]
FROM CTE T1
LEFT JOIN CTE T2 ON T1.RN>=T2.RN AND T1.RN-T2.RN<@DAYCOUNT
GROUP BY T1.RN,T1.代码,T1.成交额,T1.成交量,T1.时间
)T ON A.RN=T.RN
END另外,那段代码是,表自左连接,用于聚合统计时间比自己小的总成交额及成交量的,@DAYCOUNT限制不能超过他的行数
存储过程
USE [gupian]
GO
/****** Object: StoredProcedure [dbo].[cijunjia] Script Date: 11/13/2014 17:44:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cijunjia]
@CODE VARCHAR(50)--代码
,@DAYCOUNT INT--天数
AS
BEGIN
DECLARE @COUNT INT
SELECT @COUNT=SUM(1)FROM linshi WHERE 代码=@CODE
IF @COUNT<@DAYCOUNT
BEGIN
RETURN
END
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY 时间)RN,* FROM linshi WHERE 代码=@CODE
)
UPDATE A
SET [日均价]=T.[n1次均价]
FROM
CTE A JOIN
(SELECT T1.RN,SUM(CONVERT(float,T2.[成交额(元)]))/SUM(CONVERT(float,T2.[成交量(股)]))[n1次均价]
FROM CTE T1
LEFT JOIN CTE T2 ON T1.RN>=T2.RN AND T1.RN-T2.RN<@DAYCOUNT
GROUP BY T1.RN,T1.代码,T1.[成交额(元)],T1.[成交量(股)],T1.时间
)T ON A.RN=T.RN
END
120之前的不update 条件应该怎么加呢
@CODE VARCHAR(50)--代码
,@DAYCOUNT INT--天数
AS
BEGIN
DECLARE @COUNT INT
SELECT @COUNT=SUM(1)FROM linshi WHERE 代码=@CODE
IF @COUNT<@DAYCOUNT
BEGIN
RETURN
END
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY 时间)RN,* FROM linshi WHERE 代码=@CODE
)
UPDATE A
SET [日均价]=T.[n1次均价]
FROM
CTE A JOIN
(SELECT T1.RN,SUM(CONVERT(float,T2.[成交额(元)]))/SUM(CONVERT(float,T2.[成交量(股)]))[n1次均价]
FROM CTE T1
LEFT JOIN CTE T2 ON T1.RN>=T2.RN AND T1.RN-T2.RN<@DAYCOUNT
WHERE T1.RN>@DAYCOUNT
GROUP BY T1.RN,T1.代码,T1.[成交额(元)],T1.[成交量(股)],T1.时间
)T ON A.RN=T.RN
END最好是加在红色的那个位置