MySQL左联接查询显示相同的值。

Question

我正在尝试这样做，以便下面的查询可以计数另一个具有用户名的表中的行数，然后是有杀手的死亡数。stats表中每个用户名有1行，而pvp表中有多行带有用户名。因为某些原因，死亡栏和死亡栏的数量相同，有人知道为什么吗？这是我的问题。这里有一个sql小提琴，我想我做对了，idk如何使用sqlfiddle：http://sqlfiddle.com/#!2/b793b/1

SELECT 
    *,
    COUNT(pvptable.killer) as kills,   
    COUNT(pvptable.username) as deaths,
    ROUND(COUNT(pvptable.killer) / COUNT(pvptable.username), 2) as kd
FROM
    stats as st
    LEFT JOIN pvp as pvptable ON pvptable.killer=st.username
WHERE
    st.username="Username";

Answer 1

SELECT st.*,
       SUM(pvp.killer = st.username) AS kills,
       SUM(pvp.username = st.username) as deaths
FROM stats AS st
LEFT JOIN pvp ON pvp.username = st.username OR pvp.killer = st.username
WHERE st.username = "Username"

演示

要为每个人做这件事，请使用GROUP BY

SELECT st.*,
       SUM(pvp.killer = st.username) AS kills,
       SUM(pvp.username = st.username) as deaths
FROM stats AS st
LEFT JOIN pvp ON pvp.username = st.username OR pvp.killer = st.username
GROUP BY st.username

演示

Answer 2

COUNT(foo)和COUNT(bar)都计算了结果集中的行数，在这两种情况下，您都可以说是COUNT(*)。

例如，你需要COUNT(*) FROM ... GROUP BY killer来获取杀手的数量。不知道表格的结构是什么给你一个完整的答案。