sql-server - SQL查询崩溃短信

Question

我想执行查询，但每次运行时它都崩溃。我必须将select中的数据放入5列。对于每个“towid”，我需要创建4个列(towid，MagId，(前一个月的金额)，(两个月前的金额)，(三个月前的金额))。现在，使用游标不是一个好主意，因为它们给服务器增加了沉重的负担。

IF OBJECT_ID('dbo.StanyNaMiesiac', 'U') IS NOT NULL 
    DROP TABLE dbo.StanyNaMiesiac; 

 

CREATE TABLE dbo.StanyNaMiesiac 
(
    TowId numeric(9,0),
    MagId numeric(9,0),
    Miesiac1 decimal(15,4),
    Miesiac2 decimal(15,4),
    Miesiac3 decimal(15,4)
);

 

DECLARE @sp_Date DATETIME
SET @sp_Date = DateAdd(MONTH, -1, getdate())

 

DECLARE @sp_Date2 DATETIME
SET @sp_Date2 = DateAdd(MONTH, -2, getdate())

 

DECLARE @sp_Date3 DATETIME
SET @sp_Date3 = DateAdd(MONTH, -3, getdate())

 

DECLARE @sp_Month1 decimal(15,4)
DECLARE @sp_Month2 decimal(15,4)
DECLARE @sp_Month3 decimal(15,4)
DECLARE @Mag INT
DECLARE @towid INT
DECLARE kursor CURSOR FOR

 

Select TowId from dbo.Towar Where Aktywny !=0 AND CenaDet !=0 AND EXISTS(SELECT * FROM Towar WHERE TypTowaru <> 2)
    OPEN kursor
        FETCH NEXT FROM kursor INTO @towid
            WHILE @@FETCH_STATUS=0
            BEGIN 
                DECLARE kursor2 CURSOR FOR
                Select MagId from dbo.Magazyn
                OPEN kursor2
                    FETCH NEXT FROM kursor2 INTO @Mag
                        WHILE @@FETCH_STATUS=0
                        BEGIN 
                            Select P.towid, D.MagId, SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc
                            FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                            WHERE P.towid = @towid
                            AND D.Data >= @sp_Date
                            AND D.Data < getdate()
                            /*AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)*/
                            AND D.MagId IN (@Mag)
                            GROUP BY D.MagId, P.TowId
                                Set @sp_Month1 =(Select SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc
                                    FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                                    WHERE P.towid = @towid
                                    AND D.Data >= @sp_Date
                                    AND D.Data < getdate()
                                    /*AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)*/
                                    AND D.MagId IN (@Mag)
                                    GROUP BY D.MagId, P.TowId)
                                Set @sp_Month2 =
                                    (Select SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc2
                                    FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                                    WHERE P.towid = @towid
                                    AND D.Data >= @sp_Date2
                                    AND D.Data < @sp_Date
                                    /*AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)*/
                                    AND D.MagId IN (@Mag)
                                    GROUP BY D.MagId, P.TowId)
                                Set @sp_Month3=(Select SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc
                                    FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                                    WHERE P.towid = @towid
                                    AND D.Data >= @sp_Date3
                                    AND D.Data < @sp_Date2
                                    /*AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)*/
                                    AND D.MagId IN (@Mag)
                                    GROUP BY D.MagId, P.TowId)
                                        INSERT INTO dbo.StanyNaMiesiac (TowId, MagId, Miesiac1, Miesiac2, Miesiac3)
                                        Values(@towid, @Mag, @sp_Month1, @sp_Month2, @sp_Month3)
                    FETCH NEXT FROM kursor2 INTO @Mag
                END                 
            FETCH NEXT FROM kursor INTO @towid
            CLOSE kursor2
            DEALLOCATE kursor2
        END 
    CLOSE kursor 
DEALLOCATE kursor

有人可以帮我重建可以平稳运行的查询吗？我运行此查询的数据库大于10GB。

Answer 1

谢谢。这是我附带的:

IF OBJECT_ID('dbo.StanyNaMiesiac', 'U') IS NOT NULL 
    DROP TABLE dbo.StanyNaMiesiac; 

IF OBJECT_ID('dbo.Miesiac1', 'U') IS NOT NULL 
    DROP TABLE dbo.Miesiac1; 

IF OBJECT_ID('dbo.Miesiac2', 'U') IS NOT NULL 
    DROP TABLE dbo.Miesiac2; 

IF OBJECT_ID('dbo.Miesiac3', 'U') IS NOT NULL 
    DROP TABLE dbo.Miesiac3;

CREATE TABLE Vis.dbo.StanyNaMiesiac 
(
    TowId numeric(9,0),
    MagId numeric(9,0),
    Miesiac1 decimal(15,4),
    Miesiac2 decimal(15,4),
    Miesiac3 decimal(15,4)
);; 

CREATE TABLE Vis.dbo.Miesiac1
(
    TowId numeric(9,0),
    MagId numeric(9,0),
    Miesiac1 decimal(15,4)
);

CREATE TABLE Vis.dbo.Miesiac2 
(
    TowId numeric(9,0),
    MagId numeric(9,0),
    Miesiac2 decimal(15,4)
);
CREATE TABLE Vis.dbo.Miesiac3
(
    TowId numeric(9,0),
    MagId numeric(9,0),
    Miesiac3 decimal(15,4)
);

DECLARE @sp_Date2 DATETIME
SET @sp_Date2 = DateAdd(MONTH, -2, getdate())

DECLARE @sp_Date DATETIME
SET @sp_Date = DateAdd(MONTH, -1, getdate())

DECLARE @sp_Date3 DATETIME
SET @sp_Date3 = DateAdd(MONTH, -3, getdate())
Insert Into dbo.Miesiac1 (towid, magid, Miesiac1)
                            
Select P.towid, D.MagId, SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc
                            FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                            WHERE  
                            D.Data >= @sp_Date
                            AND D.Data < getdate()
                            AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)
                            --AND D.MagId IN (@Mag)
                            GROUP BY D.MagId, P.TowId

Insert Into dbo.Miesiac2 (towid, magid, Miesiac2)
                            
Select P.towid, D.MagId, SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc
                            FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                            WHERE  
                            D.Data >= @sp_Date2
                            AND D.Data < @sp_date
                            AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)
                            --AND D.MagId IN (@Mag)
                            GROUP BY D.MagId, P.TowId
                            

Insert Into dbo.Miesiac3 (towid, magid, Miesiac3)
                            
Select P.towid, D.MagId, SUM(ROUND(P.IloscPlus-P.IloscMinus,3)) AS Ilosc
                            FROM PozDok P INNER JOIN Dok D ON(P.DokId=D.DokId AND D.TypDok in(21, 20) AND D.Aktywny<>0)
                            WHERE  
                            D.Data >= @sp_Date3
                            AND D.Data < @sp_Date2
                            AND EXISTS(SELECT * FROM Towar WHERE TowId=P.TowId AND TypTowaru <> 2)
                            --AND D.MagId IN (@Mag)
                            GROUP BY D.MagId, P.TowId

Insert Into dbo.stanyNaMiesiac(Towid, MagID, Miesiac1, Miesiac2, Miesiac3)
Select  isnull(isnull(Miesiac1.TowId,Miesiac2.TowId),Miesiac3.TowId), isnull(isnull(Miesiac1.MagId,Miesiac2.MagId),miesiac3.MagId), Miesiac1.Miesiac1,Miesiac2.Miesiac2, Miesiac3.Miesiac3 from Miesiac1 full outer join Miesiac2 on Miesiac1.TowId = Miesiac2.TowId AND Miesiac1.MagId = Miesiac2.MagId    
                        full outer join Miesiac3 on Miesiac1.TowId = Miesiac3.TowId AND Miesiac1.MagId = Miesiac3.MagId

IF OBJECT_ID('dbo.Miesiac1', 'U') IS NOT NULL 
    DROP TABLE dbo.Miesiac1; 

IF OBJECT_ID('dbo.Miesiac2', 'U') IS NOT NULL 
    DROP TABLE dbo.Miesiac2; 

IF OBJECT_ID('dbo.Miesiac3', 'U') IS NOT NULL 
    DROP TABLE dbo.Miesiac3;