mysql - 尝试使用三个表创建子查询

Question

您好，我正在使用以下查询来让用户搜索订单:

SELECT orders.*, tasks_x.task_all
        FROM orders LEFT JOIN (SELECT GROUP_CONCAT(tasks.task_name SEPARATOR ",")
            AS task_all, ordertasks.id_order 
                FROM tasks JOIN ordertasks 
                on ordertasks.id_task = tasks.id GROUP BY ordertasks.id_order) as tasks_x
                ON tasks_x.id_order = orders.id WHERE orders.order_name 
                LIKE "%keyword%" OR tasks_x.task_all LIKE "%keyword%"

他们可以通过订单名称或链接到他们正在搜索的订单的任务名称来搜索订单。但现在我还希望他们可以通过公司名称搜索订单。

这是我的客户表: http://i.imgur.com/dn98QAY.png

如果您需要的话，这是我的其他表格:

订单:

http://i.imgur.com/l9MaQJY.png

订单任务:

http://i.imgur.com/JGVqZ2h.png

任务:

http://i.imgur.com/5ywbOk1.png

我还将查询和我的表放在 sqlfiddle 上，这是链接:

http://sqlfiddle.com/#!2/7d942/2

说实话，我得到了实习主管的帮助来创建您在上面找到的子查询。所以我不知道如何实现这一目标。我想我知道如何使用连接来做到这一点，但不知道如何使用子查询。但我很高兴知道如何通过子查询来实现这一点。

Answer 1

如果订单与(任务或订单任务)之间的关系为 1:1，则此查询应该无需子查询即可实现此目的

SELECT o.*, t.task_name
FROM orders AS o
LEFT JOIN clients AS c ON c.id = o.id_client
LEFT JOIN ordertasks AS x on x.id_order = o.id
LEFT JOIN tasks AS t ON t.id = x.id_order
WHERE o.order_name LIKE "%keyword%" 
OR t.task_name LIKE "%keyword%" 
OR c.companyName LIKE "%keyword%";

如果订单与(任务或订单任务)之间的关系为 1:n，其中 n 可以大于 1，那么此查询应该可以解决问题

SELECT c.companyName, x.task_all, o.*
FROM orders AS o 
LEFT JOIN (
    SELECT GROUP_CONCAT(t.task_name) AS task_all, x.id_order 
    FROM tasks AS t
    INNER JOIN ordertasks AS x on x.id_task = t.id
    WHERE t.task_name LIKE "%wau%"
    GROUP BY x.id_order
) AS x ON x.id_order = o.id
LEFT JOIN clients AS c ON c.id = o.id_client
WHERE o.order_name LIKE "%wau%" 
OR x.id_order IS NOT NULL
OR c.companyName LIKE "%wau%"

注意:我不同意使用LIKE操作来查找记录，因为LIKE“%keyword%”将不使用索引“这意味着MySQL将执行整个表每次“每个表”扫描。如果表中有很多记录，尝试使用 LIKE 搜索会非常慢

为了提高速度，您可以在列上使用全文索引，然后使用 MATCH() AGAINST() 逻辑来搜索记录，而不是使用 LIKE

如果你想走那条路，那么你必须执行以下操作

1. 在orders.order_name上添加全文索引
2. 在task.task_name上添加全文索引
3. 在clients.companyName 上添加全文索引

以下是您必须运行的查询

ALTER TABLE orders ADD FULLTEXT INDEX order_name (order_name);
ALTER TABLE tasks ADD FULLTEXT INDEX task_name (task_name);
ALTER TABLE tasks ADD FULLTEXT INDEX company_name (companyName);

那么第一个查询将是这样的

SELECT o.*, t.task_name
FROM orders AS o
LEFT JOIN clients AS c ON c.id = o.id_client
LEFT JOIN ordertasks AS x on x.id_order = o.id
LEFT JOIN tasks AS t ON t.id = x.id_order
WHERE MATCH('keyword') AGAINST(o.order_name)
OR MATCH('keyword') AGAINST(t.task_name)
OR MATCH('keyword') AGAINST(c.companyName);

第二个选项是这样的

SELECT c.companyName, x.task_all, o.*
FROM orders AS o 
LEFT JOIN (
    SELECT GROUP_CONCAT(t.task_name) AS task_all, x.id_order 
    FROM tasks AS t
    INNER JOIN ordertasks AS x on x.id_task = t.id
    WHERE MATCH('wau') AGAINST(t.task_name)
    GROUP BY x.id_order
) AS x ON x.id_order = o.id
LEFT JOIN clients AS c ON c.id = o.id_client
WHERE o.order_name LIKE "%wau%" 
OR x.id_order IS NOT NULL
OR c.companyName LIKE "%wau%"