gpt4 book ai didi

Mysql group post_id 按最大计数状态

转载 作者:可可西里 更新时间:2023-11-01 07:59:30 25 4
gpt4 key购买 nike

你好,我有一个 mysql 查询,它会根据状态的最大计数返回 post_id,它当前显示的结果是

POST_ID

3

Mysql 表:

(`post_id`, `user_id`, `status`, `date_created`)
(2, 2, 'funny', 20121022120627)
(2, 3, 'lame', 20121023120627)
(3, 1, 'useful', 20121023120627)
(3, 3, 'lame', 20121023120627)
(3, 4, 'useful', 20121023120627)
(4, 4, 'useful', 20121024120627)

但我需要的结果是,按 desc 顺序显示从最大到最小的计数。如果 2 个结果具有相同的数字,那么顶部结果将基于最新的 date_created 类似这样

POST_ID

3
2
4

我当前的查询是

SELECT post_id
FROM tableName
GROUP BY post_ID
HAVING COUNT(*) =
(
SELECT MAX(x.counts)
FROM
(
SELECT post_id, COUNT(*) counts
FROM tableName
GROUP BY post_id
) x
)

示例可以在 SQLFiddle SQLFiddle 上找到

最佳答案

But the result i need is, to display the count from max to min on desc order.if 2 result having same number, then the top result will be based on the latest date_created.

您需要像这样ORDER BY COUNT(*) DESC, date_created DESC:

SELECT post_id
FROM tableName
GROUP BY post_ID
HAVING COUNT(*) =
(
SELECT MAX(x.counts)
FROM
(
SELECT post_id, COUNT(*) counts
FROM tableName
GROUP BY post_id
) x
)
ORDER BY COUNT(*) DESC, date_created DESC

更新 1

试试这个,

SELECT post_id
FROM tableName
GROUP BY post_ID
ORDER BY COUNT(*) DESC, MAX(`date_created`) DESC

Updated SQL Fiddle Demo

关于Mysql group post_id 按最大计数状态,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13044490/

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