gpt4 book ai didi

mysql Max有多个实例

转载 作者:行者123 更新时间:2023-11-29 13:39:12 24 4
gpt4 key购买 nike

我正在寻找一种方法来适应寻找最大值和最小值的多个结果。我找到了上一个问题的链接: max Counts

给出的答案之一是:

SELECT  color_id, COUNT(color_id) totalCount
FROM products
WHERE item_id = 1234
GROUP BY color_id
HAVING COUNT(color_id) =
(
SELECT COUNT(color_id) totalCount
FROM products
WHERE item_id = 1234
GROUP BY color_id
ORDER BY totalCount DESC
LIMIT 1
)

这种做法是否被接受,特别是对于大型数据库?如果有意义的话,上面的查询基本上不是在自身内部运行吗?

我有一个更复杂的查询,还需要找到 ma 和 min。我想优化它:

编辑:

SELECT `system_users`.`first`, `system_users`.`last`,  COUNT(`quotes`.`created_by`) as most_quotes
FROM `quotes`
INNER JOIN `system_users`
ON `quotes`.`created_by` = `system_users`.`id`
where `system_users`.`store_id` = '$createdID'
and `quotes`.`date_created` between '$startDate' and '$endDate' group by(`created_by`)
HAVING count(`quotes`.`created_by`) =
(
SELECT COUNT(`quotes`.`created_by`)
FROM `quotes`
INNER JOIN `system_users`
ON `quotes`.`created_by` = `system_users`.`id`
where `system_users`.`store_id` = '$createdID'
and `quotes`.`date_created` between '$startDate' and '$endDate' group by(`created_by`) ORDER BY count(`created_by`) DESC limit 1
)
OR
(
SELECT COUNT(`quotes`.`created_by`)
FROM `quotes`
INNER JOIN `system_users`
ON `quotes`.`created_by` = `system_users`.`id`
where `system_users`.`store_id` = '$createdID'
and `quotes`.`date_created` between '$startDate' and '$endDate' group by(`created_by`) ORDER BY count(`created_by`) ASC limit 1
)
ORDER BY most_quotes ASC

我正在尝试寻找不同的方法来找到最大值和最小值,但到目前为止还没有运气。对此的任何更多帮助将不胜感激谢谢MC

最佳答案

这是一个坏主意 - 在大型数据库上使用 HAVING。而且,此外,你的问题可以这样解决(我有MySQL 5.5版本):

SELECT  
color_id,
COUNT(color_id) AS totalCount
FROM
products
WHERE
item_id = 1234
GROUP BY
color_id
ORDER BY
totalCount DESC
LIMIT 1

HAVING 的问题在于它是在整个查询完成后执行的,即存储引擎已经工作,因此无法对 HAVING 进行索引或其他优化条件 - 因此,它可以被视为完整的结果集扫描。

感谢@GordonLinoff,我发现这并不完全是您想要的东西。如果您试图找到所有相应的行,您最好按照戈登的建议行事。

虽然我找到了另一种方法来解决这个问题,但它可能只比带有 HAVING 的原始变体好一些(而且 - 更好,因为存储引擎两次都会涉及)

SELECT
first.color_id,
first.rows_count
FROM
(SELECT color_id, COUNT(1) AS rows_count FROM products WHERE item_id=1234 GROUP BY color_id) AS first
LEFT JOIN
(SELECT color_id, COUNT(1) AS rows_count FROM products WHERE item_id=1234 GROUP BY color_id ORDER BY rows_count DESC LIMIT 1) AS second
ON first.rows_count=second.rows_count
WHERE second.rows_count IS NOT NULL;

我还有带有变量的变体(类似于戈登的变体)。因此您可以在这些选项之间进行选择。

关于mysql Max有多个实例,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18335336/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com