02

02

关注
发信
关注(28)粉丝(399)

php - 使用“IN”子句的多个联接

嗨，我正在尝试查询上游中继和下游客户设备之间的连接。
上游中继端口11/1 12/1---聚合器设备---客户端口1/1 1/2

                                  ---Customer ports 2/1 2/2

                                  ---Customer ports 3/1 3/2

                                  --etc

我想搜索一个客户设备并让它返回一个客户正在连接的端口列表。
客户需要连接到上游干线端口以及其他所有客户端口。
（网络有东西两侧）
输出表应该类似于：
东线
客户1端口1/1->中继11/1
客户1端口1/1->客户2端口2/1
客户1端口1/1->客户3端口3/1
等
西线
客户1端口1/2->中继12/1
客户1端口1/2->客户2端口2/2
客户1端口1/2->客户3端口3/2
等
我的连接表在源端有上游端口，在目标端有客户端口。
聚合器通过源端的核心连接和目标端的AGG连接连接到核心设备。
我可以查询每一个位来给我想要的，但无法找到将它们结合在一起的最佳方式。我是否应该尝试将它们合并到单个查询中？
有更好的办法吗？我还提供了示例数据sql。
我的问题是：
//get trunk ports
$q1 = "select system_name_dest, slot_dest, port_dest, side FROM     connections where system_name_dest IN (select distinct system_name_source     FROM connections where system_name_dest = '01-42C3:N:QLD:7NET:TOWNSVILLE')";

输出：
系统名称端插槽端端口端
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 12 01东
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 24 01瓦
//get all customer ports
$q2 = "select system_name_source, slot_source, port_source, side FROM connections where system_name_source IN (select distinct system_name_source
  FROM connections where system_name_dest ='01-42C3:N:QLD:7NET:TOWNSVILLE')";

输出：
系统名称源插槽源端口源端
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 13 01东
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 14 01瓦
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01东
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01瓦
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 02东
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 02瓦
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 06 01东
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 07 01西
 //get A-side ports
$q3 = "select system_name_source, slot_source, port_source, side
 FROM connections where system_name_dest='01-42C3:N:QLD:7NET:TOWNSVILLE'";

输出
系统名称源插槽源端口源端
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01东
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01瓦
我希望我的最后一张桌子是这样的：
其中，我有一个源端口和相应连接的东列表，以及一个具有相应连接的西列表。
源插槽端口目标插槽端口侧
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 12 01 E
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 13 01 E
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 02 E
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 06 01 E
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 24 01瓦
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 14 01瓦
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 02 02 W
02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 0102-46C1:P:QLD:TLS:TOWNSVIL_AGGR 07 01瓦
如果有帮助，还包括数据库表的子集转储。
-- phpMyAdmin SQL Dump
-- version 4.2.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 18, 2015 at 01:50 AM
-- Server version: 5.6.25-log
-- PHP Version: 5.6.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `dvn2`
--

-- --------------------------------------------------------

--
-- Table structure for table `connections`
--

CREATE TABLE IF NOT EXISTS `connections` (
`id` smallint(5) NOT NULL,
  `system_name_source` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `port_type` char(15) COLLATE utf8_unicode_ci NOT NULL,
  `slot_source` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `port_source` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `system_name_dest` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `slot_dest` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `port_dest` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `cable` char(17) COLLATE utf8_unicode_ci NOT NULL,
  `side_name` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `side` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=1007 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `connections`
--

INSERT INTO `connections` (`id`, `system_name_source`, `port_type`, `slot_source`, `port_source`, `system_name_dest`, `slot_dest`, `port_dest`, `cable`, `side_name`, `side`, `status`) VALUES
(15, '01-08C3:P:QLD:TLS:CHARLTTE_AGGR', '10G Trunk', '16', '01', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '12', '01', 'D 08C3 46C1 DD001', '01-47C5:P:QLD:TLS:WGABBA_AGGR', 'E', 1),
(202, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '10G Trunk', '13', '01', '01-11C4:P:QLD:TEL:WILLOWS_STDM', '11', '01', 'D 11C4 46C1 DD001', '', 'E', 1),
(203, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '10G Trunk', '14', '01', '01-11C4:P:QLD:TEL:WILLOWS_STDM', '12', '01', 'D 11C4 46C1 DD002', '', 'W', 1),
(204, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '01', '01', '01-42C3:N:QLD:7NET:TOWNSVILLE', '05', '01', 'D 42C3 46C1 DD001', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'E', 1),
(205, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '02', '01', '01-42C3:N:QLD:7NET:TOWNSVILLE', '05', '02', 'D 42C3 46C1 DD002', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'W', 1),
(218, '01-47C5:P:QLD:TLS:WGABBA_AGGR', '10G Trunk', '16', '01', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '24', '01', 'D 46C1 47C5 DD001', '01-08C3:P:QLD:TLS:CHARLTTE_AGGR', 'W', 1),
(626, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '01', '02', '01-13C4:P:QLD:SCA:TOWNSVILLE', '11', '01', 'D 13C4 46C1 DD001', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'E', 0),
(627, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '02', '02', '01-13C4:P:QLD:SCA:TOWNSVILLE', '12', '01', 'D 13C4 46C1 DD002', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'W', 0),
(825, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '06', '01', '01-24C5:P:QLD:BAUS:MT_STUART', '11', '01', 'D 24C5 46C1 DD001', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'E', 0),
(826, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '07', '01', '01-24C5:P:QLD:BAUS:MT_STUART', '12', '01', 'D 24C5 46C1 DD002', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'W', 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `connections`
--
ALTER TABLE `connections`
 ADD PRIMARY KEY (`id`), ADD KEY `system_name_source` (`system_name_source`), ADD KEY `system_name_dest` (`system_name_dest`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `connections`
--
ALTER TABLE `connections`
MODIFY `id` smallint(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1007;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

亲切的问候
奈杰尔

最佳答案

这将提供与最终表格相同的结果集-但顺序不同，如果重要的话，您可能需要使用它：

select c1.system_name_source, c1.slot_source, c1.port_source, '<-->', c2.system_name_source, c2.slot_dest, c2.port_dest, c1.side
FROM connections c1
JOIN connections c2 ON (c1.system_name_source=c2.system_name_dest) AND c1.side=c2.side AND c1.id!=c2.id
where c1.system_name_dest='01-42C3:N:QLD:7NET:TOWNSVILLE'
UNION
select c1.system_name_source, c1.slot_source, c1.port_source, '<-->', c2.system_name_source, c2.slot_source, c2.port_source, c1.side
FROM connections c1
JOIN connections c2 ON (c1.system_name_source=c2.system_name_source) AND c1.side=c2.side AND c1.id!=c2.id
where c1.system_name_dest='01-42C3:N:QLD:7NET:TOWNSVILLE'
ORDER BY side

关于php - 使用“IN”子句的多个联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32054805/

10-15 23:47
Powered by LMLPHP ©2024 02 0.027256