mysql用一个select短语连接多个表

6yt4nkrj 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(243)

http://sqlfiddle.com/#!9/708aef/8/0号
我有一个名为mmsusbribers的表，用于存储来自用户的信息。然后我有另外3个表：driftinfo\u subscriber\u operator\u lookup，driftinfo\u subscriber\u reseller\u lookup和driftinfo\u subscriber\u server\u lookup。我想把所有的表连接在一起。表如下所示：

CREATE TABLE IF NOT EXISTS `mmsubscribers` (
`id` int(11) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`hash` varchar(100) DEFAULT NULL,
`status` varchar(50) DEFAULT NULL,
`listid` int(11) DEFAULT NULL,
`phone` varchar(50) DEFAULT NULL,
`login` varchar(50) DEFAULT NULL,
`pass` varchar(50) DEFAULT NULL,
`language` varchar(50) DEFAULT NULL,
`orgname` varchar(75) DEFAULT NULL,
`orgadmin` int(11) DEFAULT NULL,
`domain` varchar(150) DEFAULT NULL,
`organizationId` int(11) DEFAULT NULL,
`number` varchar(15) DEFAULT NULL,
`department` varchar(50) DEFAULT NULL,
`server` varchar(20) DEFAULT NULL,
`reseller` varchar(75) DEFAULT NULL,
`operators` varchar(50) DEFAULT NULL,
`subscriber_type` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=166629 DEFAULT CHARSET=utf8;
INSERT INTO `mmsubscribers` (`id`, `email`, `name`, `hash`, `status`, 
`listid`, `phone`, `login`, `pass`, `language`, `orgname`, `orgadmin`, 
`domain`, `organizationId`, `number`, `department`, `server`, `reseller`, 
`operators`, `subscriber_type`) VALUES
(13, 'nils@gmail.com', 'Nils Nissesson', NULL, 'inactive', 2, '+4612312313', 
'user1', 'user1', 'SV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Reseller1', 
NULL, NULL),
(15, 'nils@hotmail.com', 'Nils Karlsson', 'b5q8gelmyvp4gu9sgntgvzkha0u', 'active', 2, '+47741741109874', NULL, NULL, 'EN', '', NULL, NULL, NULL, NULL, NULL, '', '', '', NULL),
(17, 'test@testing.com', 'Test Karlsson', 'mdpte4uhkvqzew4n2megoa4qk7k', 'active', 2, '+4482798273798', NULL, NULL, 'SV', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(18, 'test123@testdomain.se', 'Herr Vincze', 'mhw81k96liwlfj8lkyvu2rdr9y', 'inactive', 2, '', NULL, NULL, 'SV', '', NULL, '', NULL, '', '', '', '', '', NULL),
(146996, 'nils.nils@nils.se', 'Nils Dolk', 'dd07c78e2fa7487b283f6c4dbff7ec0fc448a4', 'active', 3, '', NULL, NULL, '', 'Test', 1, 'nils.se', 6721, '468123123454', 'Sälj', '185.XX.124.162', 'Reseller56', 'STH-UNO', 4),
 (149277, 'bo@bosse.com', 'Bo Holgersson', 'c557202473aef551d410a00d2b1be3075e8d7e1fe', 'active', 3, '', NULL, NULL, '', 'Test', 1, 'bo.se', 6578, '4653643232436', 'Företagsförsäljning', '185.39.124.154', 'Reseller1', 'STB-uno', 4),

(149824, 'support@nisse.no', 'testar', '9c5f161459236d5d216c48a47d0c2aecf1', 'active', 3, '', NULL, NULL, '', 'Test', 1, 'bo.se', 6578, '4654170399', '', '185.XX.124.162', 'Reseller1', 'Tele2', 4);

我要联接到的第一个表：

CREATE TABLE IF NOT EXISTS `driftinfo_subscriber_operator_lookup` (
  `id` int(11) NOT NULL,
  `drifinfo_problem_id` int(11) DEFAULT NULL,
  `operator` varchar(75) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumpning av Data i tabell `driftinfo_subscriber_operator_lookup`
--

INSERT INTO `driftinfo_subscriber_operator_lookup` (`id`, `drifinfo_problem_id`, `operator`) VALUES
(1, 4, 'Weblink'),
(4, 10, 'Tele2');

第二张table：

CREATE TABLE IF NOT EXISTS `driftinfo_subscriber_reseller_lookup` (
  `id` int(11) NOT NULL,
  `drifinfo_problem_id` int(11) DEFAULT NULL,
  `reseller` varchar(75) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumpning av Data i tabell `driftinfo_subscriber_reseller_lookup`
--

INSERT INTO `driftinfo_subscriber_reseller_lookup` (`id`, `drifinfo_problem_id`, `reseller`) VALUES
(1, 4, 'Reseller1'),
(2, 4, 'Reseller2'),
(5, 10, 'Reseller3'),
(6, 10, 'BestReseller'),
(7, 10, 'BadReseller');

第三张table：

CREATE TABLE IF NOT EXISTS `driftinfo_subscriber_server_lookup` (
  `id` int(11) NOT NULL,
  `drifinfo_problem_id` int(11) DEFAULT NULL,
  `server` varchar(50) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumpning av Data i tabell `driftinfo_subscriber_server_lookup`
--

INSERT INTO `driftinfo_subscriber_server_lookup` (`id`, `drifinfo_problem_id`, `server`) VALUES
(1, 4, '185.XX.124.162'),
(8, 10, '172.XX.129.20'),
(9, 10, '172.XX.129.21');

我的最后一张table：

CREATE TABLE IF NOT EXISTS `driftinfo_problem` (
  `id` int(11) NOT NULL,
  `active` varchar(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `omrade` varchar(75) DEFAULT NULL,
  `soluno_staging_message` varchar(750) DEFAULT NULL,
  `heading` varchar(100) DEFAULT NULL,
  `ingress` varchar(750) DEFAULT NULL,
  `image` varchar(1500) DEFAULT NULL,
  `text` varchar(3000) DEFAULT NULL,
  `beskrivning` varchar(250) DEFAULT NULL,
  `user_owner` varchar(75) DEFAULT NULL,
  `estimate_finished_date` date DEFAULT NULL,
  `language` varchar(50) DEFAULT NULL,
  `time` time DEFAULT NULL,
  `estimate_finished_time` time DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

--
-- Dumpning av Data i tabell `driftinfo_problem`
--

INSERT INTO `driftinfo_problem` (`id`, `active`, `date`, `omrade`, `soluno_staging_message`, `heading`, `ingress`, `image`, `text`, `beskrivning`, `user_owner`, `estimate_finished_date`, `language`, `time`, `estimate_finished_time`) VALUES
(2, 'No', '2018-04-18', 'Test Problem', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-04-18', NULL, '09:04:00', '16:00:00'),
(3, 'No', '2018-04-27', 'Operatör tysta samtal', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-04-27', NULL, '11:35:00', '14:30:00'),
(4, 'No', '2018-05-16', 'Operator driftstörning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-16', NULL, '11:20:00', '14:00:00'),
(5, 'No', '2018-05-17', 'Operator driftstörning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-17', NULL, '14:30:00', '17:00:00'),
(6, 'No', '2018-05-18', 'Internet problem', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-18', NULL, '16:00:00', '18:00:00'),
(7, 'No', '2018-05-25', 'Operator driftstörning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-25', NULL, '14:30:00', '17:00:00'),
(8, 'No', '2018-05-28', 'Operator routing problem', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-29', NULL, '16:00:00', '12:00:00'),
(10, 'Yes', '2018-05-31', 'Operator Business  störning', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2018-05-31', NULL, '10:00:00', '14:00:00');

sql小提琴
如果可能的话，我需要把这3个选项组合成1个选项。
选择1运算符：

SELECT
mmsubscribers.email,
driftinfo_problem.active,
driftinfo_problem.id,
mmsubscribers.reseller,
mmsubscribers.operators,
mmsubscribers.server
FROM mmsubscribers
INNER JOIN driftinfo_subscriber_operator_lookup ON mmsubscribers.operators = driftinfo_subscriber_operator_lookup.`operator`
INNER JOIN driftinfo_problem ON driftinfo_subscriber_operator_lookup.drifinfo_problem_id = driftinfo_problem.id
WHERE driftinfo_problem.active = 'Yes'

select2经销商：

SELECT
mmsubscribers.email,
driftinfo_problem.active,
driftinfo_problem.id,
mmsubscribers.reseller,
mmsubscribers.operators,
mmsubscribers.server
FROM mmsubscribers
INNER JOIN driftinfo_subscriber_reseller_lookup ON mmsubscribers.reseller = driftinfo_subscriber_reseller_lookup.reseller
INNER JOIN driftinfo_problem ON driftinfo_subscriber_reseller_lookup.drifinfo_problem_id = driftinfo_problem.id

选择3服务器：

SELECT
mmsubscribers.email,
driftinfo_problem.active,
driftinfo_problem.id,
mmsubscribers.reseller,
mmsubscribers.operators,
mmsubscribers.server
FROM mmsubscribers
INNER JOIN driftinfo_subscriber_server_lookup ON mmsubscribers.server = driftinfo_subscriber_server_lookup.server
INNER JOIN driftinfo_problem ON driftinfo_subscriber_server_lookup.drifinfo_problem_id = driftinfo_problem.id

我希望得到的预期结果是：

email   active  id  reseller    operators   server
nils@gmail.com  No  4   Reseller1   (null)  (null)
bo@bosse.com    No  4   Reseller1   STB-uno 185.39.124.154
support@nisse.no    No  4   Reseller1   Tele2   185.XX.124.162
nils.nils@nils.se   No  4   Reseller56  STH-UNO 185.XX.124.162
support@nisse.no    No  4   Reseller1   Tele2   185.XX.124.162
support@nisse.no    Yes 10  Reseller1   Tele2   185.XX.124.162

mysql Join select

来源：https://stackoverflow.com/questions/50676475/mysql-join-multiple-tables-with-one-select-phrase

1条答案

按热度按时间

kd3sttzy1#

SELECT
mmsubscribers.email,
driftinfo_problem.active,
driftinfo_problem.id,
mmsubscribers.reseller,
mmsubscribers.operators,
mmsubscribers.server
FROM mmsubscribers
INNER JOIN driftinfo_subscriber_operator_lookup ON mmsubscribers.operators = driftinfo_subscriber_operator_lookup.`operator`
INNER JOIN driftinfo_problem ON driftinfo_subscriber_operator_lookup.drifinfo_problem_id = driftinfo_problem.id
WHERE driftinfo_problem.active = 'Yes'
union all
SELECT
mmsubscribers.email,
driftinfo_problem.active,
driftinfo_problem.id,
mmsubscribers.reseller,
mmsubscribers.operators,
mmsubscribers.server
FROM mmsubscribers
INNER JOIN driftinfo_subscriber_reseller_lookup ON mmsubscribers.reseller = driftinfo_subscriber_reseller_lookup.reseller
INNER JOIN driftinfo_problem ON driftinfo_subscriber_reseller_lookup.drifinfo_problem_id = driftinfo_problem.id
union all
SELECT
mmsubscribers.email,
driftinfo_problem.active,
driftinfo_problem.id,
mmsubscribers.reseller,
mmsubscribers.operators,
mmsubscribers.server
FROM mmsubscribers
INNER JOIN driftinfo_subscriber_server_lookup ON mmsubscribers.server = driftinfo_subscriber_server_lookup.server
INNER JOIN driftinfo_problem ON driftinfo_subscriber_server_lookup.drifinfo_problem_id = driftinfo_problem.id;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql用一个select短语连接多个表

1条答案

相关问题

热门标签

最新问答