Có chức năng xoay vòng trong mysql không?

Có những ứng dụng có công cụ cho phép triển khai xoay vòng dữ liệu trong môi trường đồ họa thuận tiện. Ví dụ: dbForge Studio dành cho MySQL bao gồm chức năng Pivot Tables cung cấp kết quả mong muốn chỉ trong vài bước

Hãy xem ví dụ với một bảng đơn hàng được đơn giản hóa – PurchaseOrderHeader

CREATE TABLE PurchaseOrderHeader [
  PurchaseOrderID INT[11] NOT NULL,
  EmployeeID INT[11] NOT NULL,
  VendorID INT[11] NOT NULL,
  PRIMARY KEY [PurchaseOrderID]
];

INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [1, 258, 1580];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [2, 254, 1496];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [3, 257, 1494];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [4, 261, 1650];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [5, 251, 1654];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [6, 253, 1664];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [7, 255, 1678];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [8, 256, 1616];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [9, 259, 1492];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [10, 250, 1602];
INSERT PurchaseOrderHeader[PurchaseOrderID, EmployeeID, VendorID] VALUES [11, 258, 1540];
...

Giả sử rằng chúng ta cần thực hiện lựa chọn từ bảng và xác định số lượng đơn đặt hàng được thực hiện bởi một số nhân viên từ các nhà cung cấp cụ thể. Danh sách nhân viên cần thông tin – 250, 251, 252, 253, 254

Chế độ xem ưu tiên cho báo cáo như sau

Cột bên trái VendorID hiển thị ID của nhà cung cấp;

Để đạt được điều này trong dbForge Studio cho MySQL, bạn cần phải

• Thêm bảng làm nguồn dữ liệu cho biểu diễn 'Pivot Table' của tài liệu. Trong Database Explorer, bấm chuột phải vào bảng PurchaseOrderHeader và chọn Gửi đến rồi chọn Bảng Pivot trong menu bật lên
• Chỉ định một cột mà các giá trị của nó sẽ là các hàng. Kéo cột VendorID vào hộp 'Drop Rows Fields Here'
• Chỉ định một cột mà các giá trị của nó sẽ là các cột. Kéo cột EmployeeID vào hộp 'Drop Column Fields Here'. Bạn cũng có thể đặt bộ lọc cho các nhân viên được yêu cầu [250, 251, 252, 253, 254]
• Chỉ định một cột, các giá trị trong đó sẽ là dữ liệu. Kéo cột PurchaseOrderID vào hộp 'Drop Data Items Here'
• Trong các thuộc tính của cột PurchaseOrderID, hãy chỉ định loại tổng hợp – Số lượng giá trị

Chúng tôi nhanh chóng nhận được kết quả chúng tôi cần

Xoay vòng dữ liệu bằng SQL

Tất nhiên, chuyển đổi dữ liệu có thể được thực hiện bằng cơ sở dữ liệu bằng cách viết truy vấn SQL. Nhưng có một trở ngại nhỏ, MySQL không có một tuyên bố cụ thể nào cho phép làm điều này

Ví dụ dựa trên T-SQL cho SQL Server

Ví dụ: SqlServer và Oracle có toán tử PIVOT cho phép thực hiện chuyển đổi dữ liệu đó. Nếu chúng ta làm việc với SqlServer, truy vấn của chúng ta sẽ như thế này

SELECT
  VendorID
 ,[250] AS Emp1
 ,[251] AS Emp2
 ,[252] AS Emp3
 ,[253] AS Emp4
 ,[254] AS Emp5
FROM [SELECT
    PurchaseOrderID
   ,EmployeeID
   ,VendorID
  FROM Purchasing.PurchaseOrderHeader] p
PIVOT
[
  COUNT[PurchaseOrderID] FOR EmployeeID IN [[250], [251], [252], [253], [254]]
] AS t
ORDER BY t.VendorID;

Ví dụ cho MySQL

Trong MySQL, chúng ta sẽ phải sử dụng các phương tiện của SQL. Dữ liệu phải được nhóm theo cột nhà cung cấp – VendorID và đối với mỗi nhân viên được yêu cầu [EmployeeID], bạn cần tạo một cột riêng có chức năng tổng hợp

Trong trường hợp của chúng tôi, chúng tôi cần tính số lượng đơn đặt hàng, vì vậy chúng tôi sẽ sử dụng hàm tổng hợp COUNT

Trong bảng nguồn, thông tin về tất cả nhân viên được lưu trữ trong một cột EmployeeID và chúng tôi cần tính số lượng đơn đặt hàng cho một nhân viên cụ thể, vì vậy chúng tôi cần hướng dẫn hàm tổng hợp của mình chỉ xử lý một số hàng nhất định

Hàm tổng hợp không tính đến các giá trị NULL và chúng tôi sử dụng đặc thù này cho mục đích của mình

Bạn có thể sử dụng toán tử điều kiện IF hoặc CASE sẽ trả về một giá trị cụ thể cho nhân viên mong muốn, nếu không sẽ trả về NULL;

Truy vấn kết quả như sau

SELECT
  VendorID,
  COUNT[IF[EmployeeID = 250, PurchaseOrderID, NULL]] AS Emp250,
  COUNT[IF[EmployeeID = 251, PurchaseOrderID, NULL]] AS Emp251,
  COUNT[IF[EmployeeID = 252, PurchaseOrderID, NULL]] AS Emp252,
  COUNT[IF[EmployeeID = 253, PurchaseOrderID, NULL]] AS Emp253,
  COUNT[IF[EmployeeID = 254, PurchaseOrderID, NULL]] AS Emp254
FROM
  PurchaseOrderHeader p
WHERE
  p.EmployeeID BETWEEN 250 AND 254
GROUP BY
  VendorID;

Hoặc thậm chí như thế này

VendorID,
  COUNT[IF[EmployeeID = 250, 1, NULL]] AS Emp250,
  COUNT[IF[EmployeeID = 251, 1, NULL]] AS Emp251,
  COUNT[IF[EmployeeID = 252, 1, NULL]] AS Emp252,
  COUNT[IF[EmployeeID = 253, 1, NULL]] AS Emp253,
  COUNT[IF[EmployeeID = 254, 1, NULL]] AS Emp254
FROM
  PurchaseOrderHeader p
WHERE
  p.EmployeeID BETWEEN 250 AND 254
GROUP BY
  VendorID;

Khi thực hiện, một kết quả quen thuộc thu được

Tự động hóa trục dữ liệu, tạo truy vấn động

Có thể thấy, câu truy vấn có sự thống nhất nhất định, tôi. e. tất cả các cột được chuyển đổi được hình thành theo cách tương tự và để viết truy vấn, bạn cần biết các giá trị cụ thể từ bảng. Để tạo truy vấn trục, bạn cần xem lại tất cả các giá trị có thể và chỉ khi đó bạn mới nên viết truy vấn. Ngoài ra, bạn có thể chuyển tác vụ này cho máy chủ để máy chủ nhận các giá trị này và tự động thực hiện tác vụ thông thường

Hãy quay lại ví dụ đầu tiên, trong đó chúng ta đã tạo bảng mới ProductsNew từ bảng ProductsOld. Ở đó, giá trị của các thuộc tính bị hạn chế và thậm chí chúng ta không thể biết tất cả các giá trị có thể có; . Đây là các cột Thuộc tính và Giá trị, tương ứng

Toàn bộ thuật toán tạo truy vấn SQL bắt nguồn từ việc lấy các giá trị, từ đó các cột mới và các phần nối của các phần không thể thay đổi của truy vấn sẽ được hình thành

SELECT
  GROUP_CONCAT[
    CONCAT[
      ' MAX[IF[Property = ''',
      t.Property,
      ''', Value, NULL]] AS ',
      t.Property
    ]
  ] INTO @PivotQuery
FROM
  [SELECT
     Property
   FROM
     ProductOld
   GROUP BY
     Property] t;

SET @PivotQuery = CONCAT['SELECT ProductID,', @PivotQuery, ' FROM ProductOld GROUP BY ProductID'];

Biến @PivotQuery sẽ lưu trữ truy vấn của chúng ta, văn bản đã được định dạng cho rõ ràng

SELECT
  ProductID,
  MAX[IF[Property = 'Color', Value, NULL]] AS Color,
  MAX[IF[Property = 'Name', Value, NULL]] AS Name,
  MAX[IF[Property = 'ProductNumber', Value, NULL]] AS ProductNumber,
  MAX[IF[Property = 'Size', Value, NULL]] AS Size,
  MAX[IF[Property = 'SizeUnitMeasureCode', Value, NULL]] AS SizeUnitMeasureCode
FROM
  ProductOld
GROUP BY
  ProductID

Sau khi thực hiện nó, chúng ta sẽ thu được kết quả mong muốn tương ứng với sơ đồ của bảng ProductsNew.
Hơn nữa, truy vấn từ biến @PivotQuery có thể được thực thi trong tập lệnh bằng câu lệnh MySQL EXECUTE.

PREPARE statement FROM @PivotQuery;
EXECUTE statement;
DEALLOCATE PREPARE statement;

Giới thiệu về tác giả / Alexey Mikotkin

Alexey Mikotkin là kỹ sư phần mềm tại Devart. Ông lãnh đạo một nhóm phát triển các công cụ SQL Server, MySQL và Oracle. Môi trường phát triển chính là. BỌC LƯỚI. Alexey có kinh nghiệm tuyệt vời trong việc phát triển các ứng dụng client-server và GDI/DirectX. Các công nghệ mới là điểm thu hút sự quan tâm của Alexey

Bảng tổng hợp trong MySQL là gì?

Có chức năng PIVOT trong SQL không?

PIVOT và Unpivot trong MySQL là gì?

Làm cách nào để chuyển đổi hàng thành cột trong MySQL mà không cần sử dụng PIVOT?