想要批量返回 x 元素组时，MySQL 选择查询 [英] MySQL Select Query when wanting groups of x elements returned in batches

问题描述

如何通过单个查询批量返回选择结果，这应该是一个直接的复合选择.下面是一个示例表和一个简单的查询，它将通过当前使用在最终现实世界过程中无法工作的临时数字列来生成所需的结果.

How to return a select result with batches through a single query, that should be a straight forward compound select somehow. Below is a sample table and a simple query that will generate the desired result by currently using a temporary numeric column that could not work in the final real world process.

仅涉及两个关键列:ip 地址 和 oid 地址，用于该地址处的各种 SNMP 项目.需要以每个 IP 地址最多 10 个项目为一组返回结果，然后转到下一个 IP 地址并返回最多 10 个，依此类推，当一次遍历所有 IP 地址时，返回到第一个 IP并返回第二组最多10个，下一个IP和10个等等.

There are only two key columns involved: ip addresses and oid addresses for various SNMP items at that address. Need to have the returned results in groups of up to 10 items per ip address and then going to the next IP address and return up to 10 more and so on and when one pass through all the IP addresses is complete go back to the first IP and return the second group of up to 10, next IP and 10 and so on.

CREATE TABLE `test` (
 `ip` varchar(16) collate latin1_general_ci NOT NULL,
 `oid` varchar(50) collate latin1_general_ci NOT NULL,
 `element` varchar(16) collate latin1_general_ci NOT NULL,
 `temp` tinyint(4) NOT NULL,
 PRIMARY KEY  (`ip`,`oid`),
 KEY `element` (`element`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

INSERT INTO `test` VALUES 
   ('1', '1.1.1', 'a', 1),
   ('1', '1.1.2', 'b', 1),
   ('1', '1.1.3', 'c', 1),
   ('1', '1.1.4', 'd', 1),
   ('1', '1.1.5', 'e', 1),
   ('1', '1.1.6', 'f', 1),
   ('1', '1.1.7', 'g', 1),
   ('1', '1.1.8', 'h', 1),
   ('1', '1.1.9', 'i', 1),
   ('1', '1.1.10', 'j', 1),
   ('1', '1.1.11', 'k', 5),
   ('1', '1.1.12', 'l', 5),
   ('1', '1.1.13', 'm', 5),
   ('1', '1.1.14', 'n', 5),
   ('1', '1.1.15', 'o', 5),
   ('1', '1.1.16', 'p', 5),
   ('1', '1.1.17', 'q', 5),
   ('1', '1.1.18', 'r', 5),
   ('1', '1.1.19', 's', 5),
   ('1', '1.1.20', 't', 5),
   ('1', '1.1.21', 'u', 9),
   ('1', '1.1.22', 'v', 9),
   ('1', '1.1.23', 'w', 9),
   ('1', '1.1.24', 'x', 9),
   ('1', '1.1.25', 'y', 9),
   ('1', '1.1.26', 'z', 9),
   ('2', '1.1.1', 'a', 2),
   ('2', '1.1.2', 'b', 2),
   ('2', '1.1.3', 'c', 2),
   ('2', '1.1.4', 'd', 2),
   ('2', '1.1.5', 'e', 2),
   ('2', '1.1.6', 'f', 2),
   ('2', '1.1.7', 'g', 2),
   ('2', '1.1.8', 'h', 2),
   ('2', '1.1.9', 'i', 2),
   ('2', '1.1.10', 'j', 2),
   ('2', '1.1.11', 'k', 6),
   ('2', '1.1.12', 'l', 6),
   ('2', '1.1.13', 'm', 6),
   ('2', '1.1.14', 'n', 6),
   ('2', '1.1.15', 'o', 6),
   ('2', '1.1.16', 'p', 6),
   ('2', '1.1.17', 'q', 6),
   ('2', '1.1.18', 'r', 6),
   ('2', '1.1.19', 's', 6),
   ('2', '1.1.20', 't', 6),
   ('2', '1.1.21', 'u', 10),
   ('2', '1.1.22', 'v', 10),
('2', '1.1.23', 'w', 10),
('2', '1.1.24', 'x', 10),
('2', '1.1.25', 'y', 10),
('2', '1.1.26', 'z', 10),
('3', '1.2.1', 'a', 3),
('3', '1.2.2', 'b', 3),
('3', '1.2.3', 'c', 3),
('3', '1.2.4', 'd', 3),
('3', '1.2.5', 'e', 3),
('3', '1.2.6', 'f', 3),
('3', '1.2.7', 'g', 3),
('3', '1.2.8', 'h', 3),
('3', '1.2.9', 'i', 3),
('3', '1.2.10', 'j', 3),
('3', '1.2.11', 'k', 7),
('3', '1.2.12', 'l', 7),
('3', '1.2.13', 'm', 7),
('3', '1.2.14', 'n', 7),
('3', '1.2.15', 'o', 7),
('3', '1.2.16', 'p', 7),
('3', '1.2.17', 'q', 7),
('3', '1.2.18', 'r', 7),
('3', '1.2.19', 's', 7),
('3', '1.2.20', 't', 7),
('3', '1.2.21', 'u', 11),
('3', '1.2.22', 'v', 11),
('3', '1.2.23', 'w', 11),
('3', '1.2.24', 'x', 11),
('3', '1.2.25', 'y', 11),
('3', '1.2.26', 'z', 11),
('4', '1.2.1', 'a', 4),
('4', '1.2.2', 'b', 4),
('4', '1.2.3', 'c', 4),
('4', '1.2.4', 'd', 4),
('4', '1.2.5', 'e', 4),
('4', '1.2.6', 'f', 4),
('4', '1.2.7', 'g', 4),
('4', '1.2.8', 'h', 4),
('4', '1.2.9', 'i', 4),
('4', '1.2.10', 'j', 4),
('4', '1.2.11', 'k', 8),
('4', '1.2.12', 'l', 8),
('4', '1.2.13', 'm', 8),
('4', '1.2.14', 'n', 8),
('4', '1.2.15', 'o', 8),
('4', '1.2.16', 'p', 8),
('4', '1.2.17', 'q', 8),
('4', '1.2.18', 'r', 8),
('4', '1.2.19', 's', 8),
('4', '1.2.20', 't', 8),
('4', '1.2.21', 'u', 12),
('4', '1.2.22', 'v', 12),
('4', '1.2.23', 'w', 12),
('4', '1.2.24', 'x', 12),
('4', '1.2.25', 'y', 12),
('4', '1.2.26', 'z', 12);

查询:

  SELECT `ip` , `oid` , `element`
    FROM `test`
ORDER BY `temp` ASC , `ip` ASC , `oid` ASC
   LIMIT 999

以下选择查询返回所需的结果现在只需要弄清楚如何删除临时列并创建一个生成相同或相似结果的选择查询.

The following select query returns the desired result now just need to figure out how to remove the temp column and create a select query that generates the same or similar result.

任何帮助将不胜感激

推荐答案

需要以每个 IP 地址最多 10 个项目为一组返回结果，然后转到下一个 IP 地址并返回最多 10 个，依此类推，当一次遍历所有 IP 地址时，返回到第一个IP并返回第二组最多10个，下一个IP和10个等等.

Need to have the returned results in groups of up to 10 items per ip address and then going to the next IP address and return up to 10 more and so on and when one pass through all the IP addresses is complete go back to the first IP and return the second group of up to 10, next IP and 10 and so on.

不幸的是，MySQL 不支持大多数其他品牌的数据库支持的 ROW_NUMBER() 函数，但您可以使用用户变量来模拟它.

MySQL unfortunately does not support the ROW_NUMBER() function that most other brands of database support, but you can simulate it with a user variable.

以下是用您的数据和 MySQL 5.1.49 测试的:

The following is tested with your data and MySQL 5.1.49:

SET @rownum := 0;
SET @ip := null;

SELECT * FROM (
    SELECT IF(@ip=ip,@rownum:=@rownum+1,@rownum:=0) AS rownum, @ip:=ip AS ip, oid
    FROM test ORDER BY ip, oid
) AS t
ORDER BY FLOOR(rownum/10), ip, oid;

这篇关于想要批量返回 x 元素组时，MySQL 选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！