如何通过单个查询批量返回选择结果,这应该是一个直接的复合选择。下面是一个示例表和一个简单的查询,它将通过当前使用在最终现实世界过程中无法工作的临时数字列来生成所需的结果。
仅涉及两个关键列:ip addresses
and oid addresses
对于该地址处的各种 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);
Query:
SELECT `ip` , `oid` , `element`
FROM `test`
ORDER BY `temp` ASC , `ip` ASC , `oid` ASC
LIMIT 999
以下选择查询返回所需的结果
现在只需要弄清楚如何删除临时列并创建一个生成相同或相似结果的选择查询。
任何帮助,将不胜感激