Mariadb
子查詢進入連接問題
我正在努力轉換我的查詢以擺脫子查詢。我知道不使用子查詢更好,但我更改此查詢的第一個原因是因為我的 ORM(Doctrine)不能使用任何子查詢連接,它不支持它(或 CTE)。
有沒有辦法擺脫這個查詢中的子查詢?
SELECT s.id, e.exception, s.name, w.url, w.web_id, w.active, w.suspended, r.email, p.name AS partner, p.id AS partnerId, contacts.names AS contactNames, contacts.tels AS contactTels, contacts.emails AS contactEmails FROM service s JOIN web w ON s.web_id = w.id JOIN rus r ON w.rus_id = r.id JOIN partner p ON r.partner_id = p.id LEFT JOIN exception e ON e.service_id = s.id LEFT JOIN (SELECT p.id, GROUP_CONCAT(c.name) names, GROUP_CONCAT(c.tel) tels, GROUP_CONCAT(c.email) emails FROM partner p LEFT JOIN contact c ON c.partner_id = p.id WHERE c.main = 1 OR c.important = 1 GROUP BY p.id) contacts ON contacts.id = p.id LEFT JOIN contact c ON c.partner_id = p.id
表格和样本數據:
CREATE TABLE `partner` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `ico` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `created` datetime NOT NULL, `active` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `partner_idx_active` (`active`), FULLTEXT KEY `partnerEntity` (`name`,`ico`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `partner` (`id`, `name`, `ico`, `created`, `active`) VALUES (1, 'partner1', '123', '2021-05-18 22:27:24', 1); CREATE TABLE `contact` ( `id` int(11) NOT NULL AUTO_INCREMENT, `partner_id` int(11) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `tel` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `created` datetime NOT NULL, `active` int(11) NOT NULL, `main` int(11) DEFAULT NULL, `important` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `IDX_4C62E6389393F8FE` (`partner_id`), FULLTEXT KEY `contactEntity` (`name`,`email`,`tel`), CONSTRAINT `FK_4C62E6389393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `contact` (`id`, `partner_id`, `name`, `email`, `tel`, `created`, `active`, `main`, `important`) VALUES (1, 1, 'contact1', 'test@test.com', '123456789', '2021-05-18 22:28:30', 1, 1, NULL), (2, 1, 'contact2', 'test2@test.com', '123456788', '2021-05-18 22:28:48', 1, NULL, 1), (3, 1, 'contact3', 'test3@test.com', '123451234', '2021-05-18 22:29:13', 1, NULL, NULL); CREATE TABLE `rus` ( `id` int(11) NOT NULL AUTO_INCREMENT, `partner_id` int(11) DEFAULT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `active` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `IDX_3370C8199393F8FE` (`partner_id`), KEY `rus_idx_active` (`active`), FULLTEXT KEY `rusEntity` (`email`), CONSTRAINT `FK_3370C8199393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `rus` (`id`, `partner_id`, `email`, `created`, `active`) VALUES (1, 1, 'test@test.com', '2021-05-18 22:27:36', 1); CREATE TABLE `service` ( `id` int(11) NOT NULL AUTO_INCREMENT, `web_id` int(11) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `active` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `IDX_E19D9AD2FE18474D` (`web_id`), KEY `service_idx_active` (`active`), FULLTEXT KEY `serviceEntity` (`name`), CONSTRAINT `FK_E19D9AD2FE18474D` FOREIGN KEY (`web_id`) REFERENCES `web` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `service` (`id`, `web_id`, `name`, `created`, `active`) VALUES (1, 1, 'service1', '2021-05-18 22:28:08', 1); CREATE TABLE `exception` ( `id` int(11) NOT NULL AUTO_INCREMENT, `partner_id` int(11) DEFAULT NULL, `exception` longtext COLLATE utf8_unicode_ci NOT NULL, `created` datetime DEFAULT NULL, `service_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_7FC98E6D9393F8FE` (`partner_id`), KEY `FK_7FC98E6DED5CA9E6` (`service_id`), CONSTRAINT `FK_7FC98E6D9393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`), CONSTRAINT `FK_7FC98E6DED5CA9E6` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `exception` (`id`, `partner_id`, `exception`, `created`, `service_id`) VALUES (1, 1, 'test..', '2021-05-18 22:31:14', 1); CREATE TABLE `web` ( `suspended` int(11) NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `rus_id` int(11) DEFAULT NULL, `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `web_id` int(5) unsigned zerofill DEFAULT NULL, `created` datetime NOT NULL, `active` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `IDX_15C9385126907186` (`rus_id`), KEY `web_idx_active` (`active`), FULLTEXT KEY `webEntity` (`url`), CONSTRAINT `FK_15C9385126907186` FOREIGN KEY (`rus_id`) REFERENCES `rus` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `web` (`suspended`, `id`, `rus_id`, `url`, `web_id`, `created`, `active`) VALUES (0, 1, 1, 'www.test.com', 01337, '2021-05-18 22:27:54', 1);
您可以將所有其他列放在
GROUP BY
. 它更冗長,但它完成了工作。
- 您應該將所有非聚合列
SELECT
放入GROUP BY
也。SELECT
儘管MariaDB 和 MySQL 允許在ANY(...)
.contact
您現在需要在子句中進行預過濾ON
,不要在WHERE
- 目前還不清楚為什麼你有額外的
partner
和contact
查找,它們似乎沒有任何作用。SELECT s.id, e.exception, s.name, w.url, w.web_id, w.active, w.suspended, r.email, p.name AS partner, p.id AS partnerId, GROUP_CONCAT(c.name) AS contactNames, GROUP_CONCAT(c.tel) AS contactTels, GROUP_CONCAT(c.email) AS contactEmails FROM service s JOIN web w ON s.web_id = w.id JOIN rus r ON w.rus_id = r.id JOIN partner p ON r.partner_id = p.id LEFT JOIN exception e ON e.service_id = s.id LEFT JOIN contact c ON c.partner_id = p.id AND (c.main = 1 OR c.important = 1) GROUP BY s.id, e.exception, s.name, w.url, w.web_id, w.active, w.suspended, r.email, p.name, p.id;