Mariadb

子查詢進入連接問題

  • May 21, 2021

我正在努力轉換我的查詢以擺脫子查詢。我知道不使用子查詢更好，但我更改此查詢的第一個原因是因為我的 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;

引用自：https://dba.stackexchange.com/questions/291802

相關問答

Mariadb

我有一個返回結果的正確子查詢，但是當我將它用作“IN”子句時，我收到錯誤 1064

  • March 25, 2021
檢視問答
Mysql

LEFT JOIN 子查詢在主查詢中存在 EXISTS 子句時失敗

  • March 17, 2021
檢視問答
Mariadb

MariaDB：如果不存在，則將記錄（元組列表）插入表中

  • March 13, 2021
檢視問答
Mysql

MariaDB，SEQUENCE 引擎，動態上限

  • April 2, 2020
檢視問答
Mariadb

選擇與另一個表中計數相同的條目

  • July 7, 2016
檢視問答
Mariadb

MariaDB 子查詢記憶體範圍和設置

  • October 4, 2015
檢視問答