mysql - MySQL SELECT CONCAT() 列输出中的意外值

Question

我有一个 CONCAT 函数设置如下(底部的完整 SQL):

concat('劣势 (', sum(1), ')') as '焦点小组'

我希望单词 Disadvantaged 后跟方括号中的总计，因为查询已分组，即 Disadvantaged (39)

但是，我得到的是:446973616476616e74616765642028333929

这是我的完整查询:

SELECT Subject,
    concat('Disadvantaged (', sum(1), ')') as 'Focus Group',
    Avg(G1.Pointscore) as 'Average Result',
    Avg(G2.Pointscore) as 'Average KS4 Target',
    Avg(G1.Pointscore - G2.Pointscore) as 'Average Residual',
    sum(1) as 'No. Students',
    /* Attainment totals */
    sum(case when G1.Pointscore >= 7 then 1 else 0 end) as 'No. A*-A',
    sum(case when G1.Pointscore >= 5 then 1 else 0 end) as 'No. A*-C',
    /* Attainment percentages */
    sum(case when G1.Pointscore >= 7 then 1 else 0 end) / sum(1) as 'A*-A',
    sum(case when G1.Pointscore >= 5 then 1 else 0 end) / sum(1) as 'A*-C',
    /* Progress totals */
    sum(case when G1.Pointscore - G2.Pointscore > 1 then 1 else 0 end) as 'No. Sig Above',
    sum(case when G1.Pointscore - G2.Pointscore = 1 then 1 else 0 end) as 'No. Above',
    sum(case when G1.Pointscore - G2.Pointscore = 0 then 1 else 0 end) as 'No. On',
    sum(case when G1.Pointscore - G2.Pointscore = -1 then 1 else 0 end) as 'No. Below',
    sum(case when G1.Pointscore - G2.Pointscore < -1 then 1 else 0 end) as 'No. Sig Below',
    /* Progress percentages */
    sum(case when G1.Pointscore - G2.Pointscore > 1 then 1 else 0 end) / sum(1) as  'Sig Above',
    sum(case when G1.Pointscore - G2.Pointscore = 1 then 1 else 0 end) / sum(1) as 'Above',
    sum(case when G1.Pointscore - G2.Pointscore = 0 then 1 else 0 end) / sum(1) as 'On', 
    sum(case when G1.Pointscore - G2.Pointscore = -1 then 1 else 0 end) / sum(1) as 'Below',
    sum(case when G1.Pointscore - G2.Pointscore < -1 then 1 else 0 end) / sum(1) as 'Sig Below'
FROM Students S
INNER JOIN Results R ON S.UPN = R.UPN
INNER JOIN Grades G1 ON Result = G1.Grade
INNER JOIN Grades G2 ON Target = G2.Grade
WHERE Disadvantaged = 'Y'
GROUP BY Subject

更新:我在这里找到了另一篇提出并回答这个问题的帖子:

Weird behaviour of SUM and CONCAT in MySql

Answer 1

我认为您不能将 SUM 函数放入 CONCAT 函数中，而且我在本地尝试时也遇到了乱码。一种解决方法是包装您当前的查询，然后在 CONCAT 中仅使用 列，例如

SELECT t.Subject,
       CONCAT('Disadvantaged (', t.`No. Students`, ')') AS 'Focus Group',
       t.`Average Result`,
       t.`Average KS4 Target`,
       t.`Average Residual`,
       t`.No. Students`,
       ...
FROM
(
    SELECT Subject,
           AVG(G1.Pointscore) AS 'Average Result',
           AVG(G2.Pointscore) AS 'Average KS4 Target',
           AVG(G1.Pointscore - G2.Pointscore) AS 'Average Residual',
           SUM(1) AS 'No. Students',
           ...
    FROM Students S
    ...
) t

更新:

我还尝试通过以下方式切换到管道运算符进行串联:

SET sql_mode = 'PIPES_AS_CONCAT'

然后是通过 SUM(1) 连接

SELECT 'Disadvantaged (' || SUM(1) || ')'

但这也没有用。这是一个 fiddle ，似乎表明切换到管道运算符也不起作用:

SQLFiddle