T-SQL存储过程：销售排行

销售排行，从周统计表提取数据

连接类别表

CREATE   PROCEDURE   [ dbo ] . [ GetHotSales ]
@PCID   int
AS
BEGIN
IF ( @PCID =- 1 )
BEGIN
SELECT  IA.ProductCode,IA.ProductName,IA.PNum,IB.PhotoPath  FROM
(
SELECT   TOP   5  OA.ProductName,OB.ProductCode,OB.PNum
FROM
Product OA,
(
SELECT  A.ProductCode,A.PNum  FROM
(
SELECT  ProductID  AS  ProductCode,SaleAmount  AS  PNum  FROM  w_Product_Sales 
WHERE  SumType = 2
)A  WHERE  A.PNum > 0
) OB
WHERE  OA.ProductCode = OB.ProductCode
Order   By  OB.PNum  DESC
)IA
LEFT   OUTER   JOIN
(
SELECT  ProductCode,PhotoPath  FROM  w_ProductPhotos  WHERE  PhotoType = 0   and  IsMain = 1
) IB
ON  IA.ProductCode = IB.ProductCode
END
ELSE
BEGIN
SELECT  IA.ProductCode,IA.ProductName,IA.PNum,IB.PhotoPath  FROM
(
SELECT   TOP   5  OA.ProductName,OB.ProductCode,OB.PNum
FROM
Product OA,
(
SELECT  WA.ProductCode,WA.PNum  FROM
(
SELECT  A.ProductCode,A.PNum  FROM
(
SELECT  ProductID  AS  ProductCode,SaleAmount  AS  PNum  FROM  w_Product_Sales 
WHERE  SumType = 2
)A  WHERE  A.PNum > 0
) WA,
(
SELECT   DISTINCT  b.ProductCode  FROM  w_cateselect a,w_productsandcategorys b
WHERE  a.w_p_pcid = b.pcid  and  a.pcid = @PCID
)WB
WHERE  WA.ProductCode = WB.ProductCode
) OB
WHERE  OA.ProductCode = OB.ProductCode
Order   By  OB.PNum  DESC
)IA
LEFT   OUTER   JOIN
(
SELECT  ProductCode,PhotoPath  FROM  w_ProductPhotos  WHERE  PhotoType = 0   and  IsMain = 1
) IB
ON  IA.ProductCode = IB.ProductCode
END
END

 

转载于:https://www.cnblogs.com/lixx/archive/2008/09/25/1299028.html