DROP PROCEDURE IF EXISTS `proc_battleinfo`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_battleinfo`(IN userid INT,IN gid INT)
BEGIN
SET NAMES utf8mb4;
SELECT drawid,roomid,bureau,gameplayer,basescore,playmethod,
max(case when chairid=0 THEN UNIX_TIMESTAMP(concludetime) END) AS concludetime,
max(case when chairid=0 THEN nickname END) AS na0,
max(case when chairid=1 THEN nickname END) AS na1,
max(case when chairid=2 THEN nickname END) AS na2,
max(case when chairid=3 THEN nickname END) AS na3,
max(case when chairid=0 THEN id END) AS uid0,
max(case when chairid=1 THEN id END) AS uid1,
max(case when chairid=2 THEN id END) AS uid2,
max(case when chairid=3 THEN id END) AS uid3,
sum(case when chairid=0 THEN score END) AS score0,
sum(case when chairid=1 THEN score END) AS score1,
sum(case when chairid=2 THEN score END) AS score2,
sum(case when chairid=3 THEN score END) AS score3,
max(case when chairid=0 THEN CONCAT(UNIX_TIMESTAMP(starttime),gid,roomid) END) AS replay
FROM (SELECT a.drawid,b.id,b.chairid,b.score,b.starttime,b.concludetime,b.roomid,b.nickname,b.bureau,b.gameplayer,b.basescore,b.playmethod
FROM (SELECT distinct drawid FROM draw_score WHERE id=userid AND gameid = gid
AND concludetime >= date_sub(NOW(),INTERVAL 48 HOUR)) AS a left join draw_score b
ON b.drawid = a.drawid) AS aa GROUP BY drawid ORDER BY drawid DESC limit 10;
END
;;
DELIMITER ;
各位大神,看看这个存储过程能优化吗