Hướng dẫn max row mysql - mysql hàng tối đa

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

    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;
    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.

  • Kích thước hàng tối đa cho bả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;
    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à 32kb
    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;
    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 16kb
    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 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.

    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

    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,
    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à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;
    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.

  • 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

      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, xem Phần & NBSP; 15.10, định dạng hàng của InnOdb.

    • Để biết thông tin về các định dạng lưu trữ

      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;
      6, xem Phần & NBSP; 16.2.3, các định dạng lưu trữ bảng Myisam.

  • 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ụ

    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 và
    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;
    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.

    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ụ

    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;
    6 sau đây, việc thay đổi cột thành
    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 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ộ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;
    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.

    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

    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 vì thay đổi cột thành
    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 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à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;
    9.

    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;
    0
  • 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

    SELECT article, dealer, price
    FROM   shop
    WHERE  price=(SELECT MAX(price) FROM shop);
    
    +---------+--------+-------+
    | article | dealer | price |
    +---------+--------+-------+
    |    0004 | D      | 19.95 |
    +---------+--------+-------+
    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.

    Câu lệnh để tạo Bảng

    SELECT article, dealer, price
    FROM   shop
    WHERE  price=(SELECT MAX(price) FROM shop);
    
    +---------+--------+-------+
    | article | dealer | price |
    +---------+--------+-------+
    |    0004 | D      | 19.95 |
    +---------+--------+-------+
    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 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;
    1

    Câu lệnh để tạo Bảng

    SELECT article, dealer, price
    FROM   shop
    WHERE  price=(SELECT MAX(price) FROM shop);
    
    +---------+--------+-------+
    | article | dealer | price |
    +---------+--------+-------+
    |    0004 | D      | 19.95 |
    +---------+--------+-------+
    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 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;
    2

    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.

    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;
    3
  • Đối với các bảng

    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;
    6, các cộ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;
    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ộ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;
    3 mất thêm một chút, được làm tròn đến byte gần nhất.

    Câu lệnh để tạo bảng

    SELECT s1.article, s1.dealer, s1.price
    FROM shop s1
    LEFT JOIN shop s2 ON s1.price < s2.price
    WHERE s2.article IS NULL;
    4 không thành công vì
    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;
    6 yêu cầu không gian cho các cộ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;
    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, 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;
    4

    Để biết thông tin về lưu trữ cộ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;
    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;
    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à 32kb
    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;
    0 và nhỏ hơn một chút so với 16kb cho 64kB trang.

    Câu lệnh để tạo bảng

    SELECT article, dealer, price
    FROM shop
    ORDER BY price DESC
    LIMIT 1;
    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 16kb
    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.

    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;
    5