MySQL - JOIN 条件和性能问题

Question

在this fiddle ，我在JOIN子句中添加了employee_name、task_date和region的数据过滤条件。在我看来，我在这里做错了什么，因为在输出中，我看不到过滤后的数据。

我希望只看到日期 '2015-01-03' 和 '2015-01-06' 之间的数据，而当前查询返回的数据等同于日期过滤器不存在。

此外，是否有更好的方法来编写此查询，因为 fiddle 仅设计用于获取正确的 SQL，而生产环境中的数据值(value)数年且查询似乎永远不会完成(它可能运行超过 30 分钟并继续快乐地奔跑直到被杀死)。数据库针对性能进行了相当优化，以防有人想知道是否可以调整数据库。

任何指点在这里都会很有帮助。

输入/输出

代码

CREATE TABLE ForgeRock
    (`task_date` date, `employee_name` varchar(7), `task_name` varchar(55), `region` varchar(100));
INSERT INTO ForgeRock
    (`task_date`, `employee_name`, `task_name`, `region`)
VALUES
    ('2015-01-01', 'A', 'task A','USA'),
    ('2015-01-01', 'B', 'task B','Russia'),
    ('2015-01-01', 'C', 'task C','USA'),
    ('2015-01-01', 'D', 'task D','USA'),
    ('2015-01-02', 'A', 'task A','Russia'),
    ('2015-01-02', 'B', 'task B','Singapore'),
    ('2015-01-02', 'C', 'task C','USA'),
    ('2015-01-02', 'D', 'task D','USA'),
    ('2015-01-03', 'A', 'task C','Australia'),
    ('2015-01-03', 'B', 'task B','London'),
    ('2015-01-03', 'C', 'task D','USA'),
    ('2015-01-03', 'D', 'task A','USA'),
    ('2015-01-03', 'C', 'task C','London'),
    ('2015-01-04', 'A', 'task B','USA'),
    ('2015-01-04', 'B', 'task A','Singapore'),
    ('2015-01-04', 'C', 'task C','USA'),
    ('2015-01-04', 'D', 'task D','India'),
    ('2015-01-05', 'A', 'task F','USA'),
    ('2015-01-05', 'B', 'task F','USA'),
    ('2015-01-05', 'C', 'task G','China'),
    ('2015-01-05', 'D', 'task B','USA'),
    ('2015-01-06', 'A', 'task Y','USA'),
    ('2015-01-06', 'B', 'task X','USA'),
    ('2015-01-06', 'C', 'task E','USA'),
    ('2015-01-06', 'D', 'task R','USA'),
    ('2015-01-07', 'A', 'task W','China'),
    ('2015-01-07', 'B', 'task O','Russia'),
    ('2015-01-07', 'C', 'task P','USA'),
    ('2015-01-07', 'D', 'task S','London'),
    ('2015-01-07', 'C', 'task E','USA'),
    ('2015-01-08', 'A', 'task E','USA'),
    ('2015-01-08', 'B', 'task W','USA'),
    ('2015-01-08', 'C', 'task C','USA'),
    ('2015-01-08', 'D', 'task B','London');

SQL 查询

SELECT   task_date, 
         employee_name, 
         Group_concat(task_name) 
FROM     ( 
             SELECT DISTINCT a.task_date, 
                             a.employee_name, 
                             CASE 
                                 WHEN b.employee_name IS NOT NULL
                                     AND c.employee_name IS NULL THEN NULL
                                 ELSE a.task_name
                             END AS task_name 
             FROM forgerock AS a 
             LEFT OUTER JOIN forgerock AS b 
                 ON  a.employee_name = b.employee_name = 'A'
                 AND a.task_date >= '2015-01-03' 
                 AND a.task_date <= '2015-01-06' 
                 AND b.task_date >= '2015-01-03' 
                 AND b.task_date <= '2015-01-06' 
                 AND a.task_date - 1 = b.task_date
                 AND a.region = b.region = 'USA' 
             LEFT OUTER JOIN forgerock AS c 
                 ON  a.employee_name = c.employee_name = 'A'
                 AND a.task_date >= '2015-01-03' 
                 AND a.task_date <= '2015-01-06' 
                 AND c.task_date >= '2015-01-03' 
                 AND c.task_date <= '2015-01-06' 
                 AND a.task_date - 1 = c.task_date
                 AND a.task_name <> c.task_name 
                 AND a.region = c.region = 'USA' 
             ORDER BY a.task_date, 
                      a.employee_name, 
                      a.task_name) AS temp 
GROUP BY task_date, 
         employee_name

Answer 1

无论日期如何，连接都在完整表“a”上，因此您需要添加位置(请参阅 **)并且无需将其放入连接中。我不确定，但带有 IS NULL 的 CASE 并非每次都有效，我更喜欢使用合并:

    SELECT   task_date, 
         employee_name, 
         Group_concat(task_name) 
FROM     ( 
                         SELECT DISTINCT a.task_date, 
                                         a.employee_name, 
                                         CASE 
                                                         WHEN b.employee_name IS NOT NULL
                                                                 AND  COALESCE(c.employee_name, '00') THEN '00'
                                                         ELSE a.task_name
                                         END       AS task_name 
                         FROM            forgerock AS a 
                         LEFT OUTER JOIN forgerock AS b 
                         ON              a.employee_name = b.employee_name = 'A'
                         AND             b.task_date >= '2015-01-03' 
                         AND             b.task_date <= '2015-01-06' 
                         AND             a.task_date - 1 = b.task_date
                         AND             a.region = b.region = 'USA' 
                         LEFT OUTER JOIN forgerock AS c 
                         ON              a.employee_name = c.employee_name = 'A'
                         AND             c.task_date >= '2015-01-03' 
                         AND             c.task_date <= '2015-01-06' 
                         AND             a.task_date - 1 = c.task_date
                         AND             a.task_name <> c.task_name 
                         AND             a.region = b.region = 'USA' 
                         **WHERE  a.task_date >= '2015-01-03' AND   a.task_date <= '2015-01-06'**
                         ORDER BY        a.task_date, 
                                         a.employee_name, 
                                         a.task_name) AS temp 
GROUP BY task_date, 
         employee_name