php - 使用 "IN"子句的多个连接

Question

您好，我正在尝试进行查询，列出上游中继和下游客户设备之间的连接。

upstream trunk ports 11/1 12/1---Aggregator device---Customer ports 1/1 1/2

                                  ---Customer ports 2/1 2/2

                                  ---Customer ports 3/1 3/2

                                  --etc

我想搜索客户设备并让它返回客户正在连接的端口列表。客户需要连接到上游中继端口以及每个其他客户端口。(网络有东边和西边)输出表应该类似于:

EAST links

Customer1 port 1/1 -> trunk 11/1

Customer1 port1/1 -> Customer2 port 2/1

Customer1 port1/1 -> Customer3 port 3/1

etc

WEST LINKS

Customer1 port 1/2 -> trunk 12/1

Customer1 port1/2 -> Customer2 port 2/2

Customer1 port1/2 -> Customer3 port 3/2

etc

我的连接表在源端有上游端口，在目标端有客户端口。聚合器连接到核心设备，核心连接在源端，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')";

output:

system_name_dest slot_dest port_dest side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 12 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 24 01 W

//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')";

output:

system_name_source slot_source port_source side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 13 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 14 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 02 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 02 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 06 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 07 01 W

 //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'";

output

system_name_source slot_source port_source side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 W

我希望我的决赛 table 是这样的:我有一个源端口和相应连接的东列表，还有一个带有相应连接的西列表。

Source Slot Port Destination Slot Port Side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 12 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 13 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 02 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 06 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 24 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 14 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 02 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 07 01 W

如果有帮助，还包括数据库表的子集转储。

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

亲切的问候 奈杰尔

Answer 1

这会给出与您的决赛 table 相同的一组结果 - 虽然顺序不同，但如果它很重要，您可能需要玩一下:

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