主页 / dba / 问题 / 104369
Accepted
M.I.M.F
Asked: 2015-06-18 13:33:31 +0800 CST

SQL中如何获取过滤字段的总和

  • 772

如何过滤 SQL 中的值？

当我执行此查询时，我得到以下结果。

SELECT Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
       InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
FROM Stock_TBL
INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
         InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity

在此处输入图像描述

我希望像下面的屏幕截图那样过滤。我该怎么做（因为这三个表涉及并且只有 5 个商店）。

在此处输入图像描述

sql-server-2008

1 个回答

  1. Best Answer

    您想要 PIVOT StoreName。尝试这样的事情：

    SELECT * FROM (
  SELECT
    Stock_TBL.StockID,
    Stock_TBL.ItemCode,
    Stock_TBL.ItemDescription,
    InvoiceItems_TBL.StoreName,
    InvoiceItems_TBL.Quantity
  FROM Stock_TBL
  INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
  INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
  GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription, InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
) t
PIVOT ( SUM(Quantity)
  FOR StoreName IN (
    [E M B],
    [LB],
    [SHOP],
    [WAT],
    [NIH],
    [AJITH]
  )
) p

    请注意，您必须手动指定商店列表。如果您不想这样做，请研究动态枢轴。

    • 2

相关问题