我在MySQL表中有两个字段:
q31
q31_theme
q31
包含注释和q31_theme
主题,因此数据如下所示:
q31 q31_theme
Sunny and hot Good weather
Cold and wet Bad weather
Sunshine Good weather
Bright and sunny Good weather
No comment N/A
Foggy Bad weather
Raining Bad weather
Cloudy and cold Bad weather
我想做的是使用查询来显示数据,该查询显示以下内容:q31中的注释按字母顺序返回,但按q31_theme排序,其中主题计数按降序排列,因此看起来像这样:
q31 q31_theme Count
Cloudy and cold Bad weather 4
Cold and wet Bad weather 4
Foggy Bad weather 4
Raining Bad weather 4
Bright and sunny Good weather 3
Sunny and hot Good weather 3
Sunshine Good weather 3
No comment N/A 1
该count
列是主题中的评论数。
我尝试了以下方法,这使
SELECT q31, q31_theme, COUNT( q31_theme ) AS Count
FROM `results`
GROUP BY q31_theme
ORDER BY Count DESC
但是,这只会为每个主题带一个评论,很明显是一个分组问题(我认为),但是我无法确定如何实现期望的结果(假设有可能)。
任何和所有建议都欢迎。
select r.*, t.count
from results r
join
(
SELECT q31_theme, COUNT(*) AS Count
FROM results
where q31_theme is not null and q31_theme <> ''
GROUP BY q31_theme
) t on t.q31_theme = r.q31_theme
ORDER BY t.Count DESC,
r.q31 ASC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句