我试图以json格式返回投票结果。我的第一个sql是
SELECT
fa.pollQuestion_id,
q.title,
JSON_OBJECT(
'text', qo.title,
'total', COUNT(fao.pollQuestionOption_id)
) values
FROM pollFillAnswers fa
INNER JOIN pollFills f ON fa.pollFill_id = f.pollFill_id
INNER JOIN pollQuestions q ON fa.pollQuestion_id = q.pollQuestion_id
INNER JOIN pollFillAnswerOptions fao ON fao.pollFillAnswer_id =
fa.pollFillAnswer_id
WHERE f.poll_id = 17 AND fa.type IN (3,4,5)
GROUP BY fao.pollQuestionOption_id;
此sql返回如下内容:
pollu id标题值
163问题1{“文本”:“否”,“总计”:599}
163问题1{“文本”:“si”,“总计”:258}
在我的第二个sql中,我使用 GROUP BY pollQuestion_id
以及 GROUP_CONCAT(values)
使用此sql聚合结果
SELECT JSON_OBJECT(
'title', result.title,
'values', GROUP_CONCAT(values)
) answers
FROM (
SELECT
q.title,
JSON_OBJECT(
'text', qo.title,
'total', COUNT(fao.pollQuestionOption_id)
) values
FROM pollFillAnswers fa
INNER JOIN pollFills f ON fa.pollFill_id = f.pollFill_id
INNER JOIN pollQuestions q ON fa.pollQuestion_id = q.pollQuestion_id
INNER JOIN pollFillAnswerOptions fao ON fao.pollFillAnswer_id = fa.pollFillAnswer_id
WHERE f.poll_id = 17 AND fa.type IN (3,4,5)
GROUP BY fao.pollQuestionOption_id
) AS result
GROUP BY result.pollQuestion_id;
结果如下
163 {"title": "Question 1", "values": "{\"text\": \"No\", \"total\": 599},{\"text\": \"Si\", \"total\": 258}"}
``` `group_contact()` 在json中的所有字符串中添加斜杠,为什么?我试着用手指去划 `REPLACE()` 或者 `JSON_UNQUOTES()` 但我无法消除这种行为。
1条答案
按热度按时间jfgube3f1#
请尝试一下下面的代码。如果不需要,请将其替换为“”。