代码之家 › 专栏 › 技术社区 › Raj

销售汇总的sql查询

tsql sql-server sql

Raj · 技术社区 · 14 年前

这个问题是根据另一个问题的答案提出的 SO question, can be found here .

   Select s.pName, 
       s.ProductCode, 
       min(s.Price)                         as MinPrice, 
       sum(s.Quantity)                      as SalesQty, 
       sum(s.Price * s.Quantity)            as SalesValue, 
       isnull((select sum(Quantity) 
               from   Breakages 
               where  pGroup = 16 
                      and quantity > 0), 0) as BreakQty, 
       isnull((select sum(Price * Quantity) 
               from   Breakages 
               where  pGroup = 16), 0)      as BreakValue, 
       isnull((select CASE 
                        WHEN min(r.Quantity) != 0 THEN Sum(r.Quantity) 
                      END), 0)              as ReturnQty, 
       isnull((select sum(Price * Quantity) 
               from   SalesReturn 
               where  pGroup = 16), 0)      as ReturnValue 
from   SalesLog as s 
       INNER JOIN SalesReturn as r 
         ON r.BillDate = s.BillDate 
       INNER JOIN Breakages as b 
         ON r.BillDate = b.BillDate 
where  s.BillDate = '12-10-2010' 
       and r.BillDate = '12-10-2010' 
       and b.BillDate = '12-10-2010' 
       and s.pGroup = 16 
       and b.pGroup = 16 
       and r.pGroup = 16 
group  by s.pName, 
          s.ProductCode;

下面是上述查询的输出

Name               Code Price SalesQty SValue  BreakQty BValue  RefundQty  RQty
CDM 42GRMS.        854    15      3       45        2       0         3     30
APPLE JUICE 750ML  860    59      5      295        2       0         3     30
BISLERI WATER      865     3      5       15        2       0         3     30
PERK 35 GRMS       870    10     20      200        2       0         3     30

输出有问题,因为您可能得不到,代码=865的中断是2,870的退款是3,但所有行都有中断和退款。

你可以在我的查询中找到实验。谢谢…….等待回复

SalesRetrun表

CREATE TABLE [dbo].[SalesReturn](
    [srID] [int] IDENTITY(1,1) NOT NULL,
    [ProductCode] [int] NULL,
    [Quantity] [int] NULL,
    [pGroup] [int] NULL,
    [MemoNo] [int] NULL,
    [SalesmanID] [int] NULL,
    [Price] [int] NULL,
    [BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AddedOn] [datetime] NULL,
 CONSTRAINT [PK_SalesReturn] PRIMARY KEY CLUSTERED 
([srID] ASC) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[SalesLog](
   [SalesID] [int] IDENTITY(1,1) NOT NULL,
   [MemoNo] [int] NULL,
   [ProductCode] [int] NULL,
   [Quantity] [int] NULL,
   [Price] [int] NULL,
   [pGroup] [int] NULL,
   [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [pSize] [int] NULL,
   [BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_SalesLog] PRIMARY KEY CLUSTERED 
 (  [SalesID] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 ) ON [PRIMARY]

破损表

CREATE TABLE [dbo].[Breakages](
    [breakId] [int] IDENTITY(1,1) NOT NULL,
    [MemoNo] [int] NULL,
    [SalesmanID] [int] NULL,
    [ProductCode] [int] NULL,
    [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quantity] [int] NULL,
    [Price] [int] NULL,
    [pGroup] [int] NULL,
    [BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AddedOn] [datetime] NULL,
 CONSTRAINT [PK_Breakages_1] PRIMARY KEY CLUSTERED (
[breakId] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

我已经添加了产品表参考,并显示了所需的输出,但它显示了所有产品是否有任何销售或中断或退款发生的日期。

我不想显示没有销售、损坏或退款的行。这将减少我的报告大小。当前显示319行,但根据我的逻辑删除行(手动计算)后,它减少到16行(假数据)

SELECT  p.pName, p.pCode, MIN(p.pPrice) AS MinPrice
   , SUM(s.Quantity) AS SalesQty, SUM(s.Quantity) * MIN(p.pPrice) AS SalesValue
   , MIN(b.Quantity) AS BreakQty, MIN(b.Quantity) * MIN(p.pPrice) AS BreakValue
   , MIN(r.Quantity) AS ReturnQty, MIN(r.Quantity) * MIN(p.pPrice) AS ReturnValue
FROM    Products AS p

OUTER APPLY (SELECT SUM(s.Quantity) AS Quantity
         FROM   SalesLog AS s
         WHERE  s.BillDate = '12-10-2010'
                AND s.ProductCode = p.pCode
        ) AS s
OUTER APPLY (SELECT SUM(r.Quantity) AS Quantity
         FROM   SalesReturn AS r
         WHERE  r.BillDate = '12-10-2010'
                AND r.ProductCode = p.pCode
        ) AS r
OUTER APPLY (SELECT SUM(b.Quantity) AS Quantity
         FROM   Breakages AS b
         WHERE  b.BillDate = '12-10-2010'
                AND b.ProductCode = p.pCode
        ) AS b
WHERE  p.pGroup!=15 and p.pGroup!=16 
GROUP BY p.pName, p.pCode;

4 回复 | 直到 7 年前

JonPayne 14 年前

你在移动门柱!不过,下面是一个可能的解决方案为您更新的问题。

注:

使用更高效>或<而不是!=如果可以的话。这就是我更改p.pGroup上谓词的原因(我假设在15到16岁之间没有组)。
所有的分类都是在APPLY子查询中完成的,因此您不再需要groupby子句了。

以下是更新的查询:

SELECT  p.pName
      , p.ProductCode
      , p.Price AS MinPrice
      , s.Quantity AS SalesQty
      , s.Quantity * p.Price AS SalesValue
      , b.Quantity AS BreakQty
      , b.Quantity * p.Price AS BreakValue
      , r.Quantity AS ReturnQty
      , r.Quantity * p.Price AS ReturnValue
FROM    Products AS p
OUTER APPLY (SELECT SUM(s.Quantity) AS Quantity
             FROM   SalesLog AS s
             WHERE  s.BillDate = '12-10 2010' 
                    AND s.ProductCode = p.ProductCode
            ) AS s
OUTER APPLY (SELECT SUM(r.Quantity) AS Quantity
             FROM   SalesReturn AS r
             WHERE  r.BillDate = '12-10 2010' 
                    AND r.ProductCode = p.ProductCode
            ) AS r
OUTER APPLY (SELECT SUM(b.Quantity) AS Quantity
             FROM   Breakages AS b
             WHERE  b.BillDate = '12-10 2010' 
                    AND b.ProductCode = p.ProductCode
            ) AS b
WHERE   p.pGroup < 15
        AND p.pGroup > 16
        AND (
             s.Quantity IS NOT NULL
             OR r.Quantity IS NOT NULL
             OR b.Quantity IS NOT NULL
            )

Yellowfog 14 年前

您的子查询(例如这样的子查询)将始终为每一行获取相同的数据,因为where子句不引用外部查询中的任何内容。

   isnull((select sum(Quantity) 
           from   Breakages 
           where  pGroup = 16 
                  and quantity > 0), 0) as BreakQty

JonPayne 14 年前

我不知道你的数据,很难给出准确的答案。然而,我认为你是在追求这样的东西:

SELECT  s.pName
      , s.ProductCode
      , MIN(s.Price) AS MinPrice
      , SUM(s.Quantity) AS SalesQty
      , SUM(s.Quantity) * MIN(s.Price) AS SalesValue
      , MIN(b.Quantity) AS BreakQty
      , MIN(b.Quantity) * MIN(s.Price) AS BreakValue
      , MIN(r.Quantity) AS ReturnQty
      , MIN(r.Quantity) * MIN(s.Price) AS ReturnValue
FROM    SalesLog AS s
OUTER APPLY (SELECT SUM(r.Quantity) AS Quantity
             FROM   @SalesReturn AS r
             WHERE  r.BillDate = s.BillDate
                    AND r.ProductCode = s.ProductCode
            ) AS r
OUTER APPLY (SELECT SUM(b.Quantity) AS Quantity
             FROM   @Breakages AS b
             WHERE  b.BillDate = s.BillDate
                    AND b.ProductCode = s.ProductCode
            ) AS b
WHERE   s.BillDate = '12-10 2010'
GROUP BY s.pName
      , s.ProductCode ;

pcent 14 年前

子查询应该引用带有where子句的主查询。我认为把te sum和SalesLog.ProductCode联系起来。为了得到更准确的答案,您应该发布表结构。