我有一个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