来自 首页 2019-10-10 04:21 的文章
当前位置: 澳门太阳娱乐手机登录 > 首页 > 正文

2008技术内幕,透视数据

透视数据实际上就是行状态转为例状态

透视转变

透视数据是一种把多少从行的动静旋转为列的气象的管理。每个透视调换将涉嫌分组、扩张及聚合四个逻辑管理阶段,种种阶段皆有有关的成分:分组阶段管理相关的分组或行成分,扩展阶段管理有关的强大或列成分,聚合阶段管理相关的聚合成分和聚合函数。现在只要有一张表数据如下:

图片 1

自个儿明天亟需查询出上面包车型地铁结果:

图片 2

急需分析:需求在结果中为每一个雇教员和学生成一行记录,那就要求对Orders表中的行依照其empid列举行分组;从结果看,还亟需为每八个顾客生成二个见仁见智的结果列,那么扩张成分正是custid列;最终还索要对数码进行联谊(本例中为SUM)。以下代码是使用正规SQL实行透视转换:

SELECT empid,
  SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
  SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
  SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
  SUM(CASE WHEN custid = 'D' THEN qty END) AS D  
FROM dbo.Orders
GROUP BY empid;

※※※※※ 补充,假诺要转为动态的询问,即不定点对A、B、C、D实行透视调换呢?请看下边:

先深入分析,假使是动态查询,那么必然须求拼凑SQL语句,即对“SUM(CASE WHEN custid = 'A' THEN qty END) AS A,”这一局部进行拼接。首先想到要用“SELECT custid FROM [tempdb].[dbo].[Orders] GROUP BY  custid”把A、B、C、D等数据GROUP BY 查出来,然后对这几个数据集使用游标循环拼凑出SQL语句,不过今后还会有更利于的方法。先看四个测量检验:

DECLARE @temp NVARCHAR(50);
SET @temp = '';
SELECT  @temp = @temp + ',' + custid
FROM    ( SELECT    custid
          FROM      [tempdb].[dbo].[Orders]
          GROUP BY  custid
        ) AS T;
PRINT @temp;

上边这段SQL会输出“,A,B,C,D”,这表达了想循环读取数据集并赋值不必然要用游标,别忘了SELECT也是能够赋值的!所以透视转换的动态SQL如下:

DECLARE @sql NVARCHAR(800);
SET @sql = 'SELECT empid';
SELECT  @sql = @sql + ',SUM(CASE WHEN custid=''' + custid
        + ''' THEN qty END) AS ' + QUOTENAME(custid)
FROM    ( SELECT    custid
          FROM      [tempdb].[dbo].[Orders]
          GROUP BY  custid
        ) AS T

SET @sql = @sql + ' FROM dbo.Orders GROUP BY empid';

EXEC(@sql);

 

下边是运用T-SQL PIVOT运算符举办透视调换。SQL Server 2007引进了四个T-SQL特有的表运算符PIVOT,PIVOT运算符相同事关七个逻辑管理阶段(分组、扩大和会集)。注意,平时不间接把PIVOT运算符应用到源表,而是将其利用到一个表表明式(该表表明式只满含透视调换供给的3种成分,不含有其余属性):

SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

地点代码中PIVOT操作符并未一向操作Orders表,而是对三个名叫D的派生表举办操作,该派生表只含有透视转变来分empid、custid、qty。

先加一张测量试验表

逆透视调换

急需如下,原数据如下:

图片 3

今昔急需得到如此的数量:

图片 4

行使正规SQL实行逆透视调换。逆透视转变的正经SQL实施方案特别明确地要落实3个逻辑管理阶段:生成别本、提取成分和删除不相干的接力。

SELECT empid, custid,
  CASE custid
    WHEN 'A' THEN A
    WHEN 'B' THEN B
    WHEN 'C' THEN C
    WHEN 'D' THEN D    
  END AS qty
FROM dbo.EmpCustOrders
  CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);

 实施结果如下:

图片 5

万一还想进一步过滤掉含有null值的数量,则能够这么:

SELECT *
FROM (SELECT empid, custid,
        CASE custid
          WHEN 'A' THEN A
          WHEN 'B' THEN B
          WHEN 'C' THEN C
          WHEN 'D' THEN D    
        END AS qty
      FROM dbo.EmpCustOrders
        CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D
WHERE qty IS NOT NULL;

 使用T-SQL的UNPIVOT运算符进行逆透视调换:

SELECT empid, custid, qty
FROM dbo.EmpCustOrders
  UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATE       NOT NULL,
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),
  (30007, '20090907', 3, 'D', 30);

SELECT * FROM dbo.Orders;

分组集

GROUPING SETS附属子句:

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY
  GROUPING SETS
  (
    (empid, custid),
    (empid),
    (custid),
    ()
  );

CUBE从属子句

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY CUBE(empid, custid);

 

把那张表查出来

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;

图片 6

笔者们将 custid行调换到例 

SELECT empid,
SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
SUM(CASE WHEN custid = 'D' THEN qty END) AS D 
FROM dbo.Orders
GROUP BY empid;

图片 7

sql server 还帮忙二个子句用于 行转列 PIVOT  是以FROM内嵌 表表明式完结的

SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

PIVOT  (数据行)  for  要转的列  IN (转这两个数据)

 

本文由澳门太阳娱乐手机登录发布于首页,转载请注明出处:2008技术内幕,透视数据

关键词: