sql存储过程报错解决方案

sql存储过程报错

SQL code

USE [shop2]GO/****** 对象:  StoredProcedure [dbo].[Nop_SalesBestSellersReport]    脚本日期: 07/01/2012 13:12:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Nop_SalesBestSellersReport](    @LastDays int = 360,    @RecordsToReturn int = 10,    @OrderBy int = 1,    @CategoryWhere varchar(100))ASBEGIN    SET NOCOUNT ON    DECLARE @cmd varchar(500)        CREATE TABLE #tmp (        ID int not null identity,        ProductID int,        SalesTotalCount int,        SalesTotalAmount MONEY,        CollectCount int,        [Count] int)    INSERT #tmp (        ProductID,        SalesTotalCount,        SalesTotalAmount,        CollectCount,        [Count] )        SELECT    pv.ProductID,    SUM(ISNULL(SalesTotalCount,0)) AS  SalesTotalCount,    SUM(ISNULL(SalesTotalAmount,0)) AS  SalesTotalAmount,    SUM(ISNULL(CollectCount,0)) AS   CollectCount,    pp.[Count]FROM [Nop_ProductVariant] pv WITH (NOLOCK) LEFT JOIN (    SELECT opv.ProductVariantId,         SUM(opv.Quantity) AS SalesTotalCount,         SUM(opv.PriceExclTax) AS SalesTotalAmount    FROM [Nop_OrderProductVariant] opv    WHERE  EXISTS (SELECT  1 FROM [Nop_Order] o         WHERE  opv.OrderID = o.OrderID        AND o.CreatedOn >= DATEADD(dy, -@LastDays, GETDATE())        AND o.Deleted=0)    GROUP BY  opv.ProductVariantID) s ON  pv.ProductVariantID=s.ProductVariantIDLEFT JOIN (    SELECT ProductVariantID,        COUNT(ShoppingCartItemID) AS  CollectCount    FROM dbo.Nop_ShoppingCartItem     WHERE  ShoppingCartTypeID=2    GROUP  BY ProductVariantID) AS  sc ON  pv.ProductVariantID = sc.ProductVariantIDLEFT JOIN dbo.Nop_Product AS pp ON pv.ProductID=pp.ProductIdWHERE  pv.Published = 1 AND pv.Deleted = 0GROUP BY  pv.ProductID,pp.[Count]ORDER BY CASE @OrderBy WHEN 1 THEN SalesTotalAmount ELSE SalesTotalAmount END DESC    SET @cmd = 'SELECT TOP ' + CONVERT(varchar(10), @RecordsToReturn ) + ' * FROM #tmp WHERE 1=1 '    EXEC (@cmd)    DROP TABLE #tmpEND

消息 8127，级别 16，状态 1，过程 Nop_SalesBestSellersReport，第 23 行
ORDER BY 子句中的列 "s.SalesTotalAmount" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
消息 8127，级别 16，状态 1，过程 Nop_SalesBestSellersReport，第 23 行
ORDER BY 子句中的列 "s.SalesTotalAmount" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

单步执行可以执行

SQL code

SELECT    pv.ProductID,    SUM(ISNULL(SalesTotalCount,0)) AS  SalesTotalCount,    SUM(ISNULL(SalesTotalAmount,0)) AS  SalesTotalAmount,    SUM(ISNULL(CollectCount,0)) AS   CollectCount,    pp.[Count]FROM [Nop_ProductVariant] pv WITH (NOLOCK) LEFT JOIN (    SELECT opv.ProductVariantId,         SUM(opv.Quantity) AS SalesTotalCount,         SUM(opv.PriceExclTax) AS SalesTotalAmount    FROM [Nop_OrderProductVariant] opv    WHERE  EXISTS (SELECT  1 FROM [Nop_Order] o         WHERE  opv.OrderID = o.OrderID        AND o.CreatedOn >= DATEADD(dy, -@LastDays, GETDATE())        AND o.Deleted=0)    GROUP BY  opv.ProductVariantID) s ON  pv.ProductVariantID=s.ProductVariantIDLEFT JOIN (    SELECT ProductVariantID,        COUNT(ShoppingCartItemID) AS  CollectCount    FROM dbo.Nop_ShoppingCartItem     WHERE  ShoppingCartTypeID=2    GROUP  BY ProductVariantID) AS  sc ON  pv.ProductVariantID = sc.ProductVariantIDLEFT JOIN dbo.Nop_Product AS pp ON pv.ProductID=pp.ProductIdWHERE  pv.Published = 1 AND pv.Deleted = 0GROUP BY  pv.ProductID,pp.[Count]

[解决办法]

SQL code

USE [shop2]GO/****** 对象:  StoredProcedure [dbo].[Nop_SalesBestSellersReport]    脚本日期: 07/01/2012 13:12:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Nop_SalesBestSellersReport](    @LastDays int = 360,    @RecordsToReturn int = 10,    @OrderBy int = 1,    @CategoryWhere varchar(100))ASBEGIN    SET NOCOUNT ON    SELECT        pv.ProductID,        SUM(ISNULL(SalesTotalCount,0)) AS SalesTotalCount,        SUM(ISNULL(SalesTotalAmount,0)) AS SalesTotalAmount,        SUM(ISNULL(CollectCount,0)) AS CollectCount,        pp.[Count]    into #summary    FROM [Nop_ProductVariant] pv WITH (NOLOCK)     LEFT JOIN (        SELECT opv.ProductVariantId,            SUM(opv.Quantity) AS SalesTotalCount,             SUM(opv.PriceExclTax) AS SalesTotalAmount        FROM [Nop_OrderProductVariant] opv        WHERE  EXISTS (SELECT  1 FROM [Nop_Order] o             WHERE  opv.OrderID = o.OrderID            AND o.CreatedOn >= DATEADD(dy, -@LastDays, GETDATE())            AND o.Deleted=0)        GROUP BY  opv.ProductVariantID    ) s ON  pv.ProductVariantID=s.ProductVariantID    LEFT JOIN (        SELECT ProductVariantID,            COUNT(ShoppingCartItemID) AS  CollectCount        FROM dbo.Nop_ShoppingCartItem         WHERE  ShoppingCartTypeID=2        GROUP  BY ProductVariantID    ) AS  sc ON  pv.ProductVariantID = sc.ProductVariantID    LEFT JOIN dbo.Nop_Product AS pp ON pv.ProductID=pp.ProductId    WHERE  pv.Published = 1 AND pv.Deleted = 0    GROUP BY  pv.ProductID,pp.[Count]    SELECT IDENTITY(INT,1,1) AS ID, *    INTO #tmp    FROM #summary    ORDER BY CASE @OrderBy         WHEN 1 THEN SalesTotalAmount         ELSE SalesTotalCount END DESC    DECLARE @cmd varchar(500)    SET @cmd = 'SELECT TOP ' + CONVERT(varchar(10), @RecordsToReturn ) + ' * FROM #tmp WHERE 1=1 '    EXEC (@cmd)    DROP TABLE #tmp, #summaryendgo 
  

 

查看更多 下一篇