--试试这个看能提升多少效率,PS:尽量不要用游标
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[up_statistic_amount_detail]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE Stat_amount_detail(ID INT NOT NULL,
TDate DATETIME,
SAmountEachDay INT NOT NULL,
CAmountEachDay INT NOT NULL,
QAmountEachDay INT NOT NULL,
TAmountEachDay INT NOT NULL,
PAmountEachDay INT NOT NULL);
-- Insert statements for procedure here
DECLARE @DATE DATETIME;
DECLARE @MAXDATE DATETIME;
SET @DATE='2007-2-7 0:00:00';
SET @MAXDATE='2007-7-17 23:59:59';
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@DATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@DATE AS TDate INTO t2
FROM sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@DATE),120)
INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B
UPDATE A
SET SAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0),
QAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002'),
TAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002'),
PAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002')
FROM Stat_amount_detail A
UPDATE Stat_amount_detail
SET CAmountEachDay=SAmountEachDay-QAmountEachDay-TAmountEachDay-PAmountEachDay
SELECT*FROM Stat_amount_detail DROP TABLE t1
DROP TABLE t2
DROP TABLE Stat_amount_detail
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[up_statistic_amount_detail]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE Stat_amount_detail(ID INT NOT NULL,
TDate DATETIME,
SAmountEachDay INT NOT NULL,
CAmountEachDay INT NOT NULL,
QAmountEachDay INT NOT NULL,
TAmountEachDay INT NOT NULL,
PAmountEachDay INT NOT NULL);
-- Insert statements for procedure here
DECLARE @DATE DATETIME;
DECLARE @MAXDATE DATETIME;
SET @DATE='2007-2-7 0:00:00';
SET @MAXDATE='2007-7-17 23:59:59';
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@DATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@DATE AS TDate INTO t2
FROM sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@DATE),120)
INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B
UPDATE A
SET SAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0),
QAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002'),
TAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002'),
PAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002')
FROM Stat_amount_detail A
UPDATE Stat_amount_detail
SET CAmountEachDay=SAmountEachDay-QAmountEachDay-TAmountEachDay-PAmountEachDay
SELECT*FROM Stat_amount_detail DROP TABLE t1
DROP TABLE t2
DROP TABLE Stat_amount_detail
END
我这没这么多数据,你测试一下看看多久能执行完。
不过应该至少能提高50~100倍的速度吧~_~
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[up_statistic_amount_detail]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE Stat_amount_detail(ID INT NOT NULL,
TDate DATETIME,
SAmountEachDay INT NOT NULL,
CAmountEachDay INT NOT NULL,
QAmountEachDay INT NOT NULL,
TAmountEachDay INT NOT NULL,
PAmountEachDay INT NOT NULL);
-- Insert statements for procedure here
DECLARE @MINDATE DATETIME;
DECLARE @MAXDATE DATETIME;
SET @MINDATE='2007-2-7 0:00:00';
SET @MAXDATE='2007-7-17 23:59:59';
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@MINDATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@MINDATE AS TDate INTO t2
FROM master.dbo.sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@MINDATE),120)
INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B
SELECT ID,TDATE,TID,COUNT(1)AS NUM
INTO t3
FROM TL
WHERE TDATE>=@MINDATE AND TDATE<=@MAXDATE
GROUP BY ID,TDATE,TID
UPDATE A
SET SAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE),
QAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE AND TID='20002'),
TAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE AND TID='20002'),
PAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE AND TID='20002')
FROM Stat_amount_detail A
UPDATE Stat_amount_detail
SET CAmountEachDay=SAmountEachDay-QAmountEachDay-TAmountEachDay-PAmountEachDay
--SELECT*FROM Stat_amount_detail DROP TABLE t1
DROP TABLE t2
DROP TABLE t3
--DROP TABLE Stat_amount_detail
END
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@MINDATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@MINDATE AS TDate INTO t2
FROM master.dbo.sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@MINDATE),120)INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 BSELECT ID,TDATE,TID,COUNT(1)AS NUM
INTO t3
FROM TL
WHERE TDATE>=@MINDATE AND TDATE<=@MAXDATE
GROUP BY ID,TDATE,TID
---------------------------------------
--取得所有的ID
SELECT DISTINCT ID INTO t1 FROM TL
--取得所选时间段的所有日期,包括没有记录的日期,如果不显示没数据的日期,则可省略
SELECT TOP (DATEDIFF(dd,@MINDATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@MINDATE AS TDate INTO t2
FROM master.dbo.sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@MINDATE),120)
--list 出所有的行:ID*日期
INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B
--按照 ID,TDATE,TID 将原来的记录进行初步汇总,这样可以提升下面那段UPDATE的速度
SELECT ID,TDATE,TID,COUNT(1)AS NUM
INTO t3
FROM TL
WHERE TDATE>=@MINDATE AND TDATE<=@MAXDATE
GROUP BY ID,TDATE,TID
--------------------------------------------------
你的这个需求没法直接用INSERT插入