数据库表 t_users
id name
1 张三
2 李四
3 王五
数据库表t_kaohe
infoid kaoherenid beikaoherenid getscore
1 1 2 50
1 1 3 60
1 2 3 70
1 3 2 80我希望实现如下的效果..
考核问卷id
被考核人
考核人
总分
平均分
1 李四 张三,王五 130 65
1 王五 张三,李四 130 65因为又遇到了问题,所以再发一遍,sql语句如下,请前辈们看看哪错了,非常感谢..
SELECT k.infoid AS 考核问卷id,
u1.realname AS 被考核人, GROUP_CONCAT( u2.realname ) AS 考核人, SUM( k.getscore ) AS 总分, AVG( k.getscore ) AS 平均分
FROM t_kaohe k
LEFT JOIN t_users u1 ON u1.id = k.beikaoherenid
LEFT JOIN t_users u2 ON u2.id = k.kaoherenid
GROUP BY k.kaoherenid, k.infoid
ORDER BY k.infoid ASC , 平均分 ASC
LIMIT 0 , 30
id name
1 张三
2 李四
3 王五
数据库表t_kaohe
infoid kaoherenid beikaoherenid getscore
1 1 2 50
1 1 3 60
1 2 3 70
1 3 2 80我希望实现如下的效果..
考核问卷id
被考核人
考核人
总分
平均分
1 李四 张三,王五 130 65
1 王五 张三,李四 130 65因为又遇到了问题,所以再发一遍,sql语句如下,请前辈们看看哪错了,非常感谢..
SELECT k.infoid AS 考核问卷id,
u1.realname AS 被考核人, GROUP_CONCAT( u2.realname ) AS 考核人, SUM( k.getscore ) AS 总分, AVG( k.getscore ) AS 平均分
FROM t_kaohe k
LEFT JOIN t_users u1 ON u1.id = k.beikaoherenid
LEFT JOIN t_users u2 ON u2.id = k.kaoherenid
GROUP BY k.kaoherenid, k.infoid
ORDER BY k.infoid ASC , 平均分 ASC
LIMIT 0 , 30
SELECT
bkh as "被考核人",
GROUP_CONCAT(k.kh) as "考核人",
sum(k.score) as "总分",
avg(k.score) as "平均分"
FROM
(
SELECT
a.score,
b. NAME AS kh,
c. NAME AS bkh
FROM
t_kaohe a,
t_user b,
t_user c
WHERE
a.khid = b.id
AND a.bkhid = c.id
)AS k
GROUP BY
bkh
SELECT
infoid,
bkh as "被考核人",
GROUP_CONCAT(k.kh) as "考核人",
sum(k.score) as "总分",
avg(k.score) as "平均分"
FROM
(
SELECT
a.infoid,
a.score,
b. NAME AS kh,
c. NAME AS bkh
FROM
t_kaohe a,
t_user b,
t_user c
WHERE
a.khid = b.id
AND a.bkhid = c.id
)AS k
GROUP BY
bkh,infoid
u1.realname AS 被考核人, GROUP_CONCAT( u2.realname ) AS 考核人, SUM( k.getscore ) AS 总分, AVG( k.getscore ) AS 平均分
FROM t_kaohe k
LEFT JOIN t_users u1 ON u1.id = k.beikaoherenid
LEFT JOIN t_users u2 ON u2.id = k.kaoherenid
GROUP BY k.beikaoherenid, k.infoid
ORDER BY k.infoid ASC , 平均分 ASC 倒数第二行group by有点问题,上次你的代码是要以考核人分组,这次变被考核人了,所以也要改一下
DROP TABLE IF EXISTS `t_kaohe`;
CREATE TABLE `t_kaohe` (
`infoid` tinyint(4) NOT NULL DEFAULT '0',
`khid` tinyint(4) NOT NULL DEFAULT '0',
`bkhid` tinyint(4) NOT NULL DEFAULT '0',
`score` smallint(6) DEFAULT NULL,
PRIMARY KEY (`infoid`,`khid`,`bkhid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of t_kaohe
-- ----------------------------
INSERT INTO `t_kaohe` VALUES ('1', '1', '2', '50');
INSERT INTO `t_kaohe` VALUES ('2', '1', '3', '60');
INSERT INTO `t_kaohe` VALUES ('2', '2', '3', '70');
INSERT INTO `t_kaohe` VALUES ('4', '3', '2', '80');DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` tinyint(4) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('1', '张三');
INSERT INTO `t_user` VALUES ('2', '李四');
INSERT INTO `t_user` VALUES ('3', '王五');