为每个 SQL 作业获取最近完成的步骤

Question

Jakub Stibůrek

Asked: 2021-05-19 11:51:28 +0800 CST2021-05-19 11:51:28 +0800 CST 2021-05-19 11:51:28 +0800 CST

子查询进入连接问题

772

我正在努力转换我的查询以摆脱子查询。我知道不使用子查询更好，但我更改此查询的第一个原因是因为我的 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', '[email protected]',    '123456789',    '2021-05-18 22:28:30',  1,  1,  NULL),
(2, 1,  'contact2', '[email protected]',   '123456788',    '2021-05-18 22:28:48',  1,  NULL,   1),
(3, 1,  'contact3', '[email protected]',   '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,  '[email protected]',    '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);

小提琴手

2 个回答

Voted

Charlieface · Answer 1 · 2021-05-19T17:12:25+08:00

您可以将所有其他列放在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;

J.D. · Answer 2 · 2021-05-19T14:37:34+08:00

J.D.

2021-05-19T14:37:34+08:002021-05-19T14:37:34+08:00

如果您为子查询创建视图怎么办：

CREATE VIEW PartnerContact AS

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

...然后在您的主查询中加入该视图：

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
    PartnerContacts contacts ON contacts.id = p.id
        LEFT JOIN
    contact c ON c.partner_id = p.id

0

子查询进入连接问题

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

子查询进入连接问题

2 个回答

相关问题