MySQL:选择具有计算列最小值的行

发布于 2024-09-18 06:24:38 字数 701 浏览 10 评论 0原文

我想到的天真的做法是:

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table)
WHERE 
lev = (SELECT MIN(lev) FROM 
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table ));

然而,“(SELECT name, levenshtein(name, parameter) as lev FROM my_table)”子查询非常昂贵(巨大的表)是重复两次,这似乎效率极低。

我不知何故虽然你可以写:

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev) FROM my_temp_table);

但它似乎不起作用。

有没有一种干净的方法来优化查询速度?我错过了一些明显的事情吗?

我必须依赖临时表吗? (由于开销/复杂性,试图避免它,因为它们似乎不适合非常频繁/并发的查询)

来自 SQL 忍者的任何输入将不胜感激;)

The naive way of doing this that comes to mind would be:

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table)
WHERE 
lev = (SELECT MIN(lev) FROM 
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table ));

However the "(SELECT name, levenshtein(name, parameter) as lev FROM my_table)" subquery, which is very expensive (huge table) is repeated twice which seems horribly inefficient.

I somehow though you could write :

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev) FROM my_temp_table);

But it doesn't seem to work.

Is there a clean way to optimize that query for speed? Did I miss something obvious?

Do I have to rely on temporary tables?
(trying to avoid it due to the overhead/complexity as they don't seem appropriate for very frequent/concurrent queries)

Any input from SQL ninjas would be greatly appreciated ;)

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

扫码二维码加入Web技术交流群

发布评论

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

评论(4

你怎么这么可爱啊 2024-09-25 06:24:38
select * from
(
SELECT * 
FROM `test`.`test`
)
as temp
where compute_total_price_single=(select min(compute_total_price_single))
;

这就是我为我的问题所做的,因为它有效,我怀疑以下内容也有效:

SELECT name, lev FROM
    (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev));

我正在使用 MySQL 5。

select * from
(
SELECT * 
FROM `test`.`test`
)
as temp
where compute_total_price_single=(select min(compute_total_price_single))
;

This is what I did for my problem, since it worked I suspect the following would also work:

SELECT name, lev FROM
    (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev));

I'm using MySQL 5.

抽个烟儿 2024-09-25 06:24:38
SELECT * FROM
(
    SELECT * 
    FROM `test`.`test`
) as temp
WHERE compute_total_price_single = (SELECT MIN(compute_total_price_single));
SELECT * FROM
(
    SELECT * 
    FROM `test`.`test`
) as temp
WHERE compute_total_price_single = (SELECT MIN(compute_total_price_single));
茶色山野 2024-09-25 06:24:38

如果您只是想获取具有最小值的一行,您可以这样做:

SELECT name, levenshtein(name, *parameter*) AS lev
FROM my_table
ORDER BY lev ASC LIMIT 1;

If you're just trying to get one row with the minimum value, you could do:

SELECT name, levenshtein(name, *parameter*) AS lev
FROM my_table
ORDER BY lev ASC LIMIT 1;
勿忘初心 2024-09-25 06:24:38
SELECT name, min(levenshtein(name, *parameter)) as lev
FROM my_table
GROUP BY name;
SELECT name, min(levenshtein(name, *parameter)) as lev
FROM my_table
GROUP BY name;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文