WHERE 子句中的 MAX()

发布于 2024-09-30 13:19:29 字数 231 浏览 10 评论 0原文

以下 sql，在 WHERE 子句中使用 MAX 聚合函数，不起作用：

  SELECT 
   ID,
   title, 
   relevance

  FROM
   myTable

  WHERE
   (relevance <= MAX(relevance)/2)

任何人都可以指出我正确的方向吗？ myTable 是临时表，因此无法使用子查询再次引用。谢谢！

原文

The following sql, with the MAX aggregrate function in the WHERE clause, does not work:

  SELECT 
   ID,
   title, 
   relevance

  FROM
   myTable

  WHERE
   (relevance <= MAX(relevance)/2)

Can anyone point me in the right direction? myTable is a temporary table, so cannot be referenced again using a sub-query. Thanks!

分享到QQ

分享到微博

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

发布评论

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

り繁华旳梦境 2024-10-07 13:19:29

SET @MaxRel=(SELECT MAX(relevance)/2 FROM myTable);
SELECT ID,title,relevance FROM myTable WHERE (relevance <= @MaxRel);

愿这有帮助

SET @MaxRel=(SELECT MAX(relevance)/2 FROM myTable);
SELECT ID,title,relevance FROM myTable WHERE (relevance <= @MaxRel);

May this help

回复收藏 0 原文

一袭水袖舞倾城 2024-10-07 13:19:29

尝试使用子查询

SELECT 
   ID,
   title, 
   relevance
  FROM
   myTable
  WHERE
   (relevance <= (SELECT MAX(relevance)/2 FROM myTable))

另一种方式使用 like

SELECT 
       ID,
       title, 
       relevance
      FROM
       myTable
      GROUP BY ID
      HAVING
       (relevance <= MAX(relevance)/2)

try using sub query

SELECT 
   ID,
   title, 
   relevance
  FROM
   myTable
  WHERE
   (relevance <= (SELECT MAX(relevance)/2 FROM myTable))

another way using having like

SELECT 
       ID,
       title, 
       relevance
      FROM
       myTable
      GROUP BY ID
      HAVING
       (relevance <= MAX(relevance)/2)

回复收藏 0 原文

浅笑依然 2024-10-07 13:19:29

您需要使用子查询

SELECT 
    ID,
    title, 
    relevance
FROM
    myTable
WHERE
    (relevance <= (SELECT MAX(relevance) FROM myTable) /2)

You need to use a subquery

SELECT 
    ID,
    title, 
    relevance
FROM
    myTable
WHERE
    (relevance <= (SELECT MAX(relevance) FROM myTable) /2)

回复收藏 0 原文

天赋异禀 2024-10-07 13:19:29

你应该能够做这样的事情。（未经测试，如果这不能按预期工作，请告诉我。）

SELECT * FROM
(
    SELECT 
       ID,
       title, 
       relevance,
       IF( relevance > @max, @max := relevance, @max := @max ) AS max_relevance  
    FROM
        mytable,
        (SELECT @max:=0) m
) Q
WHERE relevance <= max_relevance / 2
;

常见 MySQL 查询，获取一些很好的代码示例。查找组内配额（每组前 N 个），了解如何在查询中使用变量的示例，如上所示。

You should be able to do something like this. (Untested, so please let me know if this doesn't work as expected.)

SELECT * FROM
(
    SELECT 
       ID,
       title, 
       relevance,
       IF( relevance > @max, @max := relevance, @max := @max ) AS max_relevance  
    FROM
        mytable,
        (SELECT @max:=0) m
) Q
WHERE relevance <= max_relevance / 2
;

Common MySQL Queries for some good code examples. Look for Within-group quotas (Top N per group) for an example of how to use variables in a query as shown above.

回复收藏 0 原文

走过海棠暮 2024-10-07 13:19:29

naresh提到的使用变量的方法是查询数据库的好方法，
因为它可以轻松理解查询。

回复收藏 0 原文

如日中天 2024-10-07 13:19:29

SELECT 
    ID,
    title, 
    relevance
FROM
    myTable inner join (SELECT MAX(relevance) as maxRel FROM myTable) A
WHERE
    (relevance <= (A.maxRel / 2))

仍应返回与以前相同的行数，因为“A”只有一行。

SELECT 
    ID,
    title, 
    relevance
FROM
    myTable inner join (SELECT MAX(relevance) as maxRel FROM myTable) A
WHERE
    (relevance <= (A.maxRel / 2))

Should still give you back the same number of rows as before, as "A" has only one row.

回复收藏 0 原文

~没有更多了~