我有一个CONCAT函数设置,如下所示(底部是完整的SQL):
concat('Disadvantaged (', sum(1), ')') as 'Focus Group'
当查询分组时,我期望单词discharginated后跟括号中的total,即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

最佳答案

我不认为你可以把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'

然后通过
SELECT 'Disadvantaged (' || SUM(1) || ')'

但这也不起作用。下面是一个小提琴,显示切换到管道操作员也不起作用:
SQLFiddle

07-24 09:38
查看更多