MySQL 相关子查询 SUM() ORDER BY

发布于 2024-12-02 00:27:12 字数 280 浏览 0 评论 0原文

是否有办法优化以下查询：

SELECT
  t1.id,
  (SELECT SUM(col1) FROM table_name_two t2 WHERE t2.name LIKE CONCAT('%',t1.name)) AS col1_count
FROM
  table_name_one t1
ORDER BY 
  col1_count DESC

使用 ORDER BY col1_count DESC 需要很长时间。

谢谢。

原文

Is there anyway to optimize the following query:

SELECT
  t1.id,
  (SELECT SUM(col1) FROM table_name_two t2 WHERE t2.name LIKE CONCAT('%',t1.name)) AS col1_count
FROM
  table_name_one t1
ORDER BY 
  col1_count DESC

Using ORDER BY col1_count DESC takes a long time.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一身骄傲 2024-12-09 00:27:12

只需在连接的 on 子句中进行正常连接即可：

SELECT
  t1.id,
  SUM(t2.col1) AS col1_count
FROM table_name_one t1
LEFT JOIN table_name_two t2 on t2.name LIKE CONCAT('%', t1.name)
GROUP BY 1
ORDER BY 2 DESC

这样应该方式更快 - 它基本上是一个查询而不是“n”个查询，尽管它赢了使用带有前导 % 的 LIKE 运算符无法从索引中获得任何帮助

Just make a normal join with your comparison in the join's on clause:

SELECT
  t1.id,
  SUM(t2.col1) AS col1_count
FROM table_name_one t1
LEFT JOIN table_name_two t2 on t2.name LIKE CONCAT('%', t1.name)
GROUP BY 1
ORDER BY 2 DESC

It should be way faster this way - it's basically one query instead of "n" queries, although it won't get any help from indexes using the LIKE operator with a leading %

回复收藏 0 原文

~没有更多了~