MySQL Select Query 当想要批量返回 x 组元素时

Question

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

只涉及两个关键列:ip 地址 和oid 地址，用于该地址的各种 SNMP 项目。需要将返回的结果分组，每个 IP 地址最多 10 个项目，然后转到下一个 IP 地址并返回最多 10 个，依此类推，当完成所有 IP 地址时，返回到第一个 IP并返回第二组最多 10 个，下一个 IP 和 10 等等。

这里有一些示例数据和简单的查询，不知何故需要成为一个复合查询

-- 表test

的表结构

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;

--

-- 转储表test

的数据

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

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

任何帮助将不胜感激

Answer 1

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