我有一个json列,名为Info,带有以下值:
{"width":"800, "height":"480"}
{"width":"800, "height":"480"}
{"width":"768, "height":"480"}
我需要一个SQL查询才能以这种格式输出:
Label |Count
==============
800x480, 2
768x480, 1
第二个值代表计数。
我设法做的是提取第一值和计数:
select info->>'width' as label, count(info->>'width') from table_name GROUP BY 1 ORDER BY 2 DESC LIMIT 5
输出:
Label |Count
==============
800, 2
768, 1
常规字符串串联应该这样做。由于某些原因,如果您从查询中省略了()
,则查询会失败。
SELECT (info->>'width') || 'x' || (info->>'width') AS label,
COUNT(info->>'width')
FROM table_name
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5