3.6.4 & nbsp; các hàng giữ mức tối đa theo nhóm của một cột nhất định
Nhiệm vụ: Đối với mỗi bài viết, hãy tìm các đại lý hoặc đại lý với giá đắt nhất.
Vấn đề này có thể được giải quyết bằng một trình điều khiển dưới mức như thế này:
SELECT article, dealer, price
FROM shop s1
WHERE price=[SELECT MAX[s2.price]
FROM shop s2
WHERE s1.article = s2.article]
ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
Ví dụ trước sử dụng một truy vấn con tương quan, có thể không hiệu quả [xem Phần & NBSP; 13.2.11.7, các nhóm phụ tương quan trực tiếp]. Các khả năng khác để giải quyết vấn đề là sử dụng một truy vấn con không tương thích trong mệnh đề
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN [
SELECT article, MAX[price] AS price
FROM shop
GROUP BY article] AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
6, SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN [
SELECT article, MAX[price] AS price
FROM shop
GROUP BY article] AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
7 hoặc biểu thức bảng chung với hàm cửa sổ.Subquery không tương quan:
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN [
SELECT article, MAX[price] AS price
FROM shop
GROUP BY article] AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN [
SELECT article, MAX[price] AS price
FROM shop
GROUP BY article] AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
8:SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN [
SELECT article, MAX[price] AS price
FROM shop
GROUP BY article] AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
8 hoạt động trên cơ sở rằng khi SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
0 ở giá trị tối đa của nó, không có SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
1 với giá trị lớn hơn và do đó giá trị SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
2 tương ứng là SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
3. Xem Phần & NBSP; 13.2.10.2, Lệnh tham gia mệnh đề.Biểu thức bảng phổ biến với hàm cửa sổ:
WITH s1 AS [
SELECT article, dealer, price,
RANK[] OVER [PARTITION BY article
ORDER BY price DESC
] AS `Rank`
FROM shop
]
SELECT article, dealer, price
FROM s1
WHERE `Rank` = 1
ORDER BY article;
3.6.2 & nbsp; hàng giữ tối đa của một cột nhất định
Nhiệm vụ: Tìm số, đại lý và giá của bài viết đắt nhất.
Điều này dễ dàng được thực hiện với một truy vấn con:
SELECT article, dealer, price
FROM shop
WHERE price=[SELECT MAX[price] FROM shop];
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
Một giải pháp khác là sử dụng
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN [
SELECT article, MAX[price] AS price
FROM shop
GROUP BY article] AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
8, như được hiển thị ở đây:SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
Bạn cũng có thể làm điều này bằng cách sắp xếp tất cả các hàng giảm dần theo giá và chỉ nhận được hàng đầu tiên bằng điều khoản
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
5 dành riêng cho MySQL, như thế này:SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
Ghi chú
Nếu có một số bài viết đắt nhất, mỗi bài có giá 19,95, giải pháp
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
5 sẽ chỉ hiển thị một trong số đó. Biểu diễn bên trong của bảng MySQL có giới hạn kích thước hàng tối đa là 65.535 byte, ngay cả khi công cụ lưu trữ có khả năng hỗ trợ các hàng lớn hơn. Các cột
7 vàSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
8 chỉ đóng góp 9 đến 12 byte đối với giới hạn kích thước hàng vì nội dung của chúng được lưu trữ riêng biệt với phần còn lại của hàng.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
Kích thước hàng tối đa cho bảng
9, áp dụng cho dữ liệu được lưu trữ cục bộ trong trang cơ sở dữ liệu, hơi ít hơn nửa trang cho cài đặt 4kb, 8kb, 16kb và 32kbSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
0. Ví dụ: kích thước hàng tối đa thấp hơn một chút so với 8kb cho kích thước trang 16kbWITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
9 mặc định. Đối với 64kb trang, kích thước hàng tối đa hơi nhỏ hơn 16kb. Xem Phần & NBSP; 15,22, Giới hạn InnoDB.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
Nếu một hàng chứa các cột có độ dài thay đổi vượt quá kích thước hàng tối đa
9,SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
9 chọn các cột có độ dài thay đổi để lưu trữ ngoài trang bên ngoài cho đến khi hàng phù hợp với giới hạn kích thước hàngSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
9. Lượng dữ liệu được lưu trữ cục bộ cho các cột có độ dài thay đổi được lưu trữ ngoài trang khác nhau theo định dạng hàng. Để biết thêm thông tin, xem Phần & NBSP; 15.10, định dạng hàng của InnOdb.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
Các định dạng lưu trữ khác nhau sử dụng số lượng khác nhau của tiêu đề trang và dữ liệu rơ moóc, ảnh hưởng đến lượng lưu trữ có sẵn cho các hàng.
Để biết thông tin về các định dạng hàng
9, xem Phần & NBSP; 15.10, định dạng hàng của InnOdb.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
Để biết thông tin về các định dạng lưu trữ
6, xem Phần & NBSP; 16.2.3, các định dạng lưu trữ bảng Myisam.WITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
Giới hạn kích thước hàng tối đa MySQL của 65,535 byte được thể hiện trong các ví dụ
9 vàSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
6 sau đây. Giới hạn được thực thi bất kể công cụ lưu trữ, mặc dù công cụ lưu trữ có thể có khả năng hỗ trợ các hàng lớn hơn.WITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
mysql> CREATE TABLE t [a VARCHAR[10000], b VARCHAR[10000], c VARCHAR[10000], d VARCHAR[10000], e VARCHAR[10000], f VARCHAR[10000], g VARCHAR[6000]] ENGINE=InnoDB CHARACTER SET latin1; ERROR 1118 [42000]: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> CREATE TABLE t [a VARCHAR[10000], b VARCHAR[10000], c VARCHAR[10000], d VARCHAR[10000], e VARCHAR[10000], f VARCHAR[10000], g VARCHAR[6000]] ENGINE=MyISAM CHARACTER SET latin1; ERROR 1118 [42000]: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
Trong ví dụ
6 sau đây, việc thay đổi cột thànhWITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
8 tránh giới hạn kích thước hàng 65,535 byte và cho phép hoạt động thành công vì các cộtSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
7 vàSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
8 chỉ đóng góp 9 đến 12 byte cho kích thước hàng.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
mysql> CREATE TABLE t [a VARCHAR[10000], b VARCHAR[10000], c VARCHAR[10000], d VARCHAR[10000], e VARCHAR[10000], f VARCHAR[10000], g TEXT[6000]] ENGINE=MyISAM CHARACTER SET latin1; Query OK, 0 rows affected [0.02 sec]
Hoạt động thành công cho bảng
9 vì thay đổi cột thànhSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
8 tránh giới hạn kích thước hàng MySQL 65,535-byte và lưu trữ ngoài trang của các cột có độ dài thay đổi tránh giới hạn kích thước hàngSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
9.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
0SELECT s1.article, dealer, s1.price FROM shop s1 JOIN [ SELECT article, MAX[price] AS price FROM shop GROUP BY article] AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;
Lưu trữ cho các cột có độ dài thay đổi bao gồm các byte dài, được tính vào kích thước hàng. Ví dụ: cột
7 mất hai byte để lưu trữ độ dài của giá trị, do đó mỗi giá trị có thể mất tới 767 byte.SELECT article, dealer, price FROM shop WHERE price=[SELECT MAX[price] FROM shop]; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+
Câu lệnh để tạo Bảng
8 thành công vì các cột yêu cầu 32.765 + 2 byte và 32.766 + 2 byte, nằm trong kích thước hàng tối đa là 65.535 byte:SELECT article, dealer, price FROM shop WHERE price=[SELECT MAX[price] FROM shop]; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+
1SELECT s1.article, dealer, s1.price FROM shop s1 JOIN [ SELECT article, MAX[price] AS price FROM shop GROUP BY article] AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;
Câu lệnh để tạo Bảng
9 không thành công bởi vì, mặc dù độ dài cột nằm trong độ dài tối đa là 65,535 byte, hai byte bổ sung được yêu cầu để ghi lại độ dài, khiến kích thước hàng vượt quá 65,535 byte:SELECT article, dealer, price FROM shop WHERE price=[SELECT MAX[price] FROM shop]; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+
2SELECT s1.article, dealer, s1.price FROM shop s1 JOIN [ SELECT article, MAX[price] AS price FROM shop GROUP BY article] AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;
Giảm chiều dài cột xuống còn 65,533 hoặc ít hơn cho phép câu lệnh thành công.
3SELECT s1.article, dealer, s1.price FROM shop s1 JOIN [ SELECT article, MAX[price] AS price FROM shop GROUP BY article] AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;
Đối với các bảng
6, các cộtWITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
3 yêu cầu không gian bổ sung trong hàng để ghi lại xem các giá trị của chúng có phải làSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
3 hay không. Mỗi cộtSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
3 mất thêm một chút, được làm tròn đến byte gần nhất.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
Câu lệnh để tạo bảng
4 không thành công vìSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
6 yêu cầu không gian cho các cộtWITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
3 ngoài không gian cần thiết cho các byte dài cột có độ dài thay đổi, khiến kích thước hàng vượt quá 65,535 byte:SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
4SELECT s1.article, dealer, s1.price FROM shop s1 JOIN [ SELECT article, MAX[price] AS price FROM shop GROUP BY article] AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;
Để biết thông tin về lưu trữ cột
9SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
3, xem Phần & NBSP; 15.10, định dạng hàng của InnoDB.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
9 Hạn chế kích thước hàng [đối với dữ liệu được lưu trữ cục bộ trong trang cơ sở dữ liệu] xuống ít hơn một nửa trang cơ sở dữ liệu cho các cài đặt 4kb, 8kb, 16kb và 32kbSELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
0 và nhỏ hơn một chút so với 16kb cho 64kB trang.WITH s1 AS [ SELECT article, dealer, price, RANK[] OVER [PARTITION BY article ORDER BY price DESC ] AS `Rank` FROM shop ] SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;
Câu lệnh để tạo bảng
1 không thành công vì các cột được xác định vượt quá giới hạn kích thước hàng cho trang 16kbSELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
9.SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;
5SELECT s1.article, dealer, s1.price FROM shop s1 JOIN [ SELECT article, MAX[price] AS price FROM shop GROUP BY article] AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;