关于POVIT的简单介绍

全栈程序员-站长 • 2026年3月26日下午1:54 • 未分类 • 阅读 2

–povit 只能用于 2005以上，所以检查一下兼容性级别要大于90.

–下面的就是查看兼容性级别的SQL语句

SELECT name,COMPATIBILITY_LEVEL

FROM sys.databases

–最重要的是最后几行，前面的都是create table，insert data的操作。

— 创建Cust表

CREATE TABLE Cust (
CustID NCHAR(7) PRIMARY KEY,
CustName NVARCHAR(20)
)

— 创建Production表

CREATE TABLE Production (
ProductionID CHAR(8) PRIMARY KEY,
ProductionName NVARCHAR(50)
)

— 创建OrderS表

CREATE TABLE OrderS (
OrderSID      INT PRIMARY KEY,
CustID      NCHAR(7),
ProductionID      NCHAR(8),
OrderSNum    INT
)

— 输入数据

— 输入Cust信息

INSERT INTO Cust VALUES (‘0000001’, ‘ABC’)
INSERT INTO Cust VALUES (‘0000002’, ‘DEF’)
INSERT INTO Cust VALUES (‘0000003’, ‘GHI’)
INSERT INTO Cust VALUES (‘0000004’, ‘JKL’)

— 输入Production信息

INSERT INTO Production VALUES (‘GK010001’, ‘CUP’)
INSERT INTO Production VALUES (‘GK020001’, ‘BED’)
INSERT INTO Production VALUES (‘GK030001’, ‘COFFEE’)
INSERT INTO Production VALUES (‘GK040001’, ‘PHONE’)

— 输入OrderS信息

INSERT INTO OrderS VALUES (1, ‘0000001’, ‘GK010001’, 3)
INSERT INTO OrderS VALUES (2, ‘0000002’, ‘GK020001’, 2)
INSERT INTO OrderS VALUES (3, ‘0000003’, ‘GK010001’, 1)
INSERT INTO OrderS VALUES (4, ‘0000003’, ‘GK030001’, 1)
INSERT INTO OrderS VALUES (5, ‘0000002’, ‘GK040001’, 1)
INSERT INTO OrderS VALUES (6, ‘0000001’, ‘GK020001’, 1)
INSERT INTO OrderS VALUES (7, ‘0000004’, ‘GK030001’, 1)
INSERT INTO OrderS VALUES (8, ‘0000004’, ‘GK040001’, 2)
INSERT INTO OrderS VALUES (9, ‘0000002’, ‘GK030001’, 4)

SELECT B.CustName

           , C.ProductionName
           , SUM(A.OrderSNum) AS SUMORDER
FROM OrderS AS A
INNER JOIN Cust AS B
             ON A.CustID = B.CustID
INNER JOIN Production AS C
             ON A.ProductionID = C.ProductionID
GROUP BY B.CustName, C.ProductionName

执行结果如下：

— 使用 PIVOT

　SELECT *

   FROM (SELECT B.CustName
                       , C.ProductionName
                       , SUM(A.OrderSNum) AS SUMORDER
                 FROM OrderS AS A
                 INNER JOIN Cust AS B
                             ON A.CustID = B.CustID
                 INNER JOIN Production AS C
                             ON A.ProductionID = C.ProductionID
                GROUP BY B.CustName, C.ProductionName) AS A
        PIVOT (SUM(SUMORDER) FOR [ProductionName] IN (CUP, BED,COFFEE,PHONE)) AS PVT