有一个表A，字段名如下：（部门A只能给其他部门评，不能给自己评）
id 自动编号
s_id1 考评部门A编号
s_id2 被考评部门B编号
a_date 考评月份(yyyymm)
a_score 得分
现在想实现查询出两个部门的互评信息，结果如下格式：
考评部门\被考评部门 部门A 部门B 部门C 部门D
部门A \ 99 98 95
部门B 95 \ 96 99
部门C 95 96 \ 98
部门D 96 98 97 \
SELECT s_id1 AS ''考评部门\被考评部门"",MAX(if(s_id2="部门A",a_score,0)) AS "部门A",
MAX(if(s_id2="部门B",a_score,0)) AS "部门B",
MAX(if(s_id2="部门C",a_score,0)) AS "部门C",
MAX(if(s_id2="部门D",a_score,0)) AS "部门D"
from A GROUP BY s_id1
我跑了下 发现2个错误 改好了
SELECT s_id1 AS 考评部门\被考评部门,MAX(if(s_id2="部门A",a_score,0)) AS "部门A",
MAX(if(s_id2="部门B",a_score,0)) AS "部门B",
MAX(if(s_id2="部门C",a_score,0)) AS "部门C"
MAX(if(s_id2="部门D",a_score,0)) AS "部门D"
from A GROUP BY s_id1
那是表的数据问题了啊 GROUP BY 以后必须用聚合函数 宿便你用AVG MIN MAX什么的 我认为就应该只有一个数据的
SELECT s_id1 AS 考评部门\被考评部门,MAX(if(s_id2=s_id1,'\',if(s_id2="部门A",a_score,0))) AS "部门A",
MAX(if(s_id2=s_id1,'\',if(s_id2="部门B",a_score,0))) AS "部门B",
MAX(if(s_id2=s_id1,'\',if(s_id2="部门C",a_score,0))) AS "部门C",
MAX(if(s_id2=s_id1,'\',if(s_id2="部门D",a_score,0))) AS "部门D",
from A where a_date = 'yyyymm' GROUP BY s_id1