如何消除SQL中的重复计算？

发布于 2024-08-08 06:27:07 字数 208 浏览 1 评论 0原文

我有一个可以简化为的 SQL：

SELECT * 
  FROM table 
 WHERE LOCATE( column, :keyword ) > 0 
ORDER BY LOCATE( column, :keyword )

您可以看到有一个重复的“LOCATE(column, :keyword)”。有没有办法只计算一次？

原文

I have a SQL that can be simplified to:

SELECT * 
  FROM table 
 WHERE LOCATE( column, :keyword ) > 0 
ORDER BY LOCATE( column, :keyword )

You can see there is a duplicate of "LOCATE( column, :keyword )". Is there a way to calculate it only once ?

分享到QQ

分享到微博

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

发布评论

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

网白 2024-08-15 06:27:07

SELECT *, LOCATE( column, :keyword ) AS somelabel 
FROM table 
WHERE somelabel > 0 
ORDER BY somelabel

SELECT *, LOCATE( column, :keyword ) AS somelabel 
FROM table 
WHERE somelabel > 0 
ORDER BY somelabel

回复收藏 0 原文

没有伤那来痛 2024-08-15 06:27:07

HAVING 与 MySQL 中的别名一起使用：

SELECT *, LOCATE( column, :keyword ) AS somelabel 
FROM table 
HAVING somelabel > 0 
ORDER BY somelabel

HAVING works with aliases in MySQL:

SELECT *, LOCATE( column, :keyword ) AS somelabel 
FROM table 
HAVING somelabel > 0 
ORDER BY somelabel

回复收藏 0 原文

黎夕旧梦 2024-08-15 06:27:07

Jeff Ober 的想法是正确的，但这里有一个替代方法：

SELECT
  t.*
 ,loc.LOCATED
FROM
  table t
  INNER JOIN
  (
  SELECT
    primary_key
   ,LOCATE(column,:keyword) AS LOCATED
  FROM
    table 
  ) loc
  ON t.primary_key = loc.primary_key
WHERE loc.LOCATED > 0
ORDER BY
  loc.LOCATED

Jeff Ober has the right idea, but here is an alternative method:

SELECT
  t.*
 ,loc.LOCATED
FROM
  table t
  INNER JOIN
  (
  SELECT
    primary_key
   ,LOCATE(column,:keyword) AS LOCATED
  FROM
    table 
  ) loc
  ON t.primary_key = loc.primary_key
WHERE loc.LOCATED > 0
ORDER BY
  loc.LOCATED

回复收藏 0 原文

~没有更多了~