我试图创建一个排行榜,但我不知道如何做mysql查询。
我想计算技能表中玩家的所有等级,得到总等级,计算经验表中玩家的所有经验,得到总经验以及显示用户列中的人名。
有三个表派系玩家,派系经验,派系技能。
这是我目前所拥有的,但不起作用:
$sql = ("SELECT a.id,
(SELECT COUNT(*) FROM factions_mcmmo_experience WHERE user_id = a.id) as TotalXP,
(SELECT COUNT(*) FROM factions_mcmmo_skills WHERE user_id = a.id) as TotalLevel
FROM (SELECT DISTINCT id FROM factions_mcmmo_users) a LIMIT 10;");
任何帮助都将不胜感激
编辑:我现在已经开始工作了,但我不确定这是否是最有效的方法,如果有人能帮我,如果有更好的方法,这将意味着很多。
我还想知道,如果数字是以千为单位,是否可以用逗号显示总exp和总level,例如:total level 5882和total xp 582882
编辑2:
我已经知道如何格式化数字,但仍然不知道我的代码是否有效
$sql = ("SELECT id, user,
(SELECT FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy),0) FROM factions_mcmmo_skills b WHERE b.user_id = a.id) as TotalLevel,
(SELECT FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy),0) FROM factions_mcmmo_experience c WHERE c.user_id = a.id) as TotalXP
FROM (SELECT id, user FROM factions_mcmmo_users) a group by id ORDER BY TotalLevel DESC, TotalXP DESC LIMIT 10;");
编辑3
从scaisEdge更新了代码,但将每个级别显示为1,XP显示为1,因此我将count(*)更改为sum,按TotalLevel降序添加了order,这似乎起了作用,但我无法让它在用户表中显示人员名称(用户列)?我不确定我是否应该换成sum,因为它没有起到相反的作用。
$sql = ("SELECT a.id, b.TotalXP, c.TotalLevel
FROM (SELECT DISTINCT id FROM factions_mcmmo_users) a
INNER JOIN (
SELECT user_id, Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy) as TotalXP
FROM factions_mcmmo_experience
GROUP By user_id
) b on b.user_id = a.id
INNER JOIN (
SELECT user_id, Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy) as TotalLevel
FROM factions_mcmmo_skills
GROUP by user_id
) c on c.user_id = a.id
ORDER BY TotalLevel DESC
LIMIT 10;");
编辑4
一切正常,但当我尝试在内部联接上使用“format(Sum(Columns),0)”格式化总计时,EXP总计似乎正常,但主总计级别不显示超过1000的结果,并且它打破了排行榜的位置,它应该在总计级别上对它们进行排序,但似乎是随机的,当您删除格式时,0它回去工作
如果数字是千,我希望它显示逗号,例如:Total Level:5532和Total EXP 5882882
观看现场演示:http://mcbuffalo.com/playground/leaderboards/server/factions-mcmmo.php
尝试使用格式的更新代码:
$sql = ("SELECT a.id, a.user, b.TotalXP, c.TotalLevel
FROM (SELECT id, user FROM factions_mcmmo_users) a
INNER JOIN (
SELECT user_id, FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy), 0) as TotalXP
FROM factions_mcmmo_experience
GROUP By user_id
) b on b.user_id = a.id
INNER JOIN (
SELECT user_id, FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy), 0) as TotalLevel
FROM factions_mcmmo_skills
GROUP by user_id
) c on c.user_id = a.id
ORDER BY TotalLevel DESC;");
编辑5
用PHP修改了数字,一切正常
原始图像
最佳答案
你可以用一些内部连接
$sql = ("SELECT a.id, a.name, b.TotalXP, c.TotalLevel
FROM (SELECT DISTINCT id, name FROM factions_mcmmo_users) a
INNER JOIN (
SELECT user_id, COUNT(*) as TotalXP
FROM factions_mcmmo_experience
GROUP By user_id
) b on b.user_id = a.id
INNER JOIN (
SELECT user_id, COUNT(*) as TotalLevel
FROM factions_mcmmo_skills
GROUP by user_id
) c on c.user_id = a.id
LIMIT 10
关于php - MYSQL PHP查询中的多个计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44219284/