SQL Server连接表和枢轴

表格1

---------------------------------------------------
    | SALEID | SOLDBY | SALEPRICE | MARGIN |   DATE   |
    |  1     |  'aa'  |  10,000   |   10   | 2013-1-1 |
    |  2     |  'bb'  |  25,000   |    5   | 2013-5-1 |

表2

---------------------------------------------------
    | SALEITEMID | SALEID | SALEPRICE | CATEGORY |
    |  1         |  1     |   6,000   | BOOKS    |
    |  2         |  1     |   4,000   | PRINTING |
    |  3         |  2     |   5,000   | BOOKS    |
    |  4         |  2     |   12,000  | PRINTING |
    |  5         |  2     |   8,000   | DVD      |

我需要一个会产生的查询

TAB3

--------------------------------------------------------------------------------
    | SALEID | SOLDBY | SALEPRICE | MARGIN |   DATE   |  BOOKS  | PRINTING | DVD
    |  1     |  'aa'  |  10,000   |   10   | 2013-1-1 |  6,000  |  4,000   | 0
    |  2     |  'bb'  |  25,000   |    5   | 2013-5-1 |  5,000  | 12,000   | 8,000

我相当新的旋转,不知道枢轴是否为此而不是.

解决方法

WITH Sales AS (
   SELECT
      S.SaleID,S.SoldBy,S.SalePrice,S.Margin,S.Date,I.SalePrice,I.Category
   FROM
      dbo.Sale S
      INNER JOIN dbo.SaleItem I
         ON S.SaleID = I.SaleID
)
SELECT *
FROM
   Sales
   PIVOT (Max(SalePrice) FOR Category IN (Books,Printing,DVD)) P
;

或者交替地：

SELECT
   S.SaleID,I.Books,I.Printing,I.DVD
FROM
   dbo.Sale S
   INNER JOIN (
      SELECT *
      FROM
         (SELECT SaleID,SalePrice,Category FROM dbo.SaleItem) I
         PIVOT (Max(SalePrice) FOR Category IN (Books,DVD)) P
   ) I ON S.SaleID = I.SaleID
;

这些具有相同的结果集,并且实际上可以被查询优化器处理相同,但可能不是.当您开始在“销售”表中放置条件时,会发生重大差异 – 您应该测试并查看哪个查询工作更好.

但是,我可以建议您在演示层中进行旋转吗？例如,如果您正在使用SSRS,则很容易使用一个可以为您进行所有枢纽的矩阵控制.这是最好的,因为那么如果你添加一个新的类别,你将不会修改你的所有SQL代码！

有一种方法可以动态地找到要转发的列名,但它涉及动态SQL.我不是真的建议,作为最好的方式,尽管这是可能的.

另一种可以工作的方法是预处理这个查询 – 这意味着在Category表上设置一个触发器,它重写一个VIEW以包含所有存在的现有类别.这解决了我提到的很多其他问题,但是再次使用表示层是最好的.

注意：如果您的列名称(以前的值)是数字或以数字开头,则必须使用PIVOT中的方括号(Max(Value)FOR CategoryId IN([1],[2],[3],[4]))P.或者,您可以修改值,然后才能到达查询的PIVOT部分,以预先填充一些字母,以便列列表不需要转义.为了进一步阅读,请查看SQL Server中标识符的规则.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!