1表结构
表里面的Pos1对应的成绩是Score1,后面以此类推
2需求,给定一个pos的值,判断其在一条记录中是否分数最高
pos的值可能出现在Pos1,Pos2,Pos3,Pos4中的任意一个字段里面
现在要判断pos的值对应的Score是否是4个值里面的最大值
3 sql语句
SELECT COUNT(*) AS '大赢家次数',(
SELECT SUM(CurCircle) FROM log WHERE Pos1=6617136 OR Pos2=6617136 OR Pos3=6617136 OR Pos4=6617136
) AS '对局次数' FROM (
SELECT Id, pos, MAX(score) AS eScore FROM (
SELECT * FROM (
SELECT Id,Pos1 AS pos,Score1 AS score FROM log WHERE Pos1=6617136 OR Pos2=6617136 OR Pos3=6617136 OR Pos4=6617136 UNION
SELECT Id,Pos2,Score2 FROM log WHERE Pos1=6617136 OR Pos2=6617136 OR Pos3=6617136 OR Pos4=6617136 UNION
SELECT Id,Pos3,Score3 FROM log WHERE Pos1=6617136 OR Pos2=6617136 OR Pos3=6617136 OR Pos4=6617136 UNION
SELECT Id,Pos4,Score4 FROM log WHERE Pos1=6617136 OR Pos2=6617136 OR Pos3=6617136 OR Pos4=6617136
)t ORDER BY score DESC, FIELD(pos,6617136) DESC
)r GROUP BY Id
) e WHERE pos=6617136 AND eScore != 0;
语法解释:
ORDER BY FIELD(pos,6617136) 是mysql自定义排序的语法
UNION 是连接2个查询结果集