mySQL 查询组合列 count()

Question

过去几个小时我一直在努力尝试将两个查询组合在一起以节省一些 PHP 代码。

http://sqlfiddle.com/#!9/fb47e0/2

编辑(更多信息!):

这就是我的表格中的数据。

account_id personnelofficer_no personnelofficer color
500079462  1                                    #000000
340345950  2                                    #555555
734356754                      1
972345435                      1
883243435                      2

我只是想列出所有人事官员以及分配给他们的所有成员。

SELECT wot_clan_members.account_name, wot_info.color, total
FROM wot_info
JOIN wot_clan_members ON wot_clan_members.account_id = wot_info.account_id
JOIN (
    SELECT COUNT(*) total 
    FROM wot_info 
    WHERE wot_info.personnelofficer <> '' 
    GROUP BY wot_info.personnelofficer
) as total
WHERE wot_clan_members.role = 'personnel_officer'

当前输出以下内容:

account_name     color   total
conan230160      #1E90FF 24
conan230160      #1E90FF 19
conan230160      #1E90FF 20
conan230160      #1E90FF 22
Woody_the_Viking #800080 24
Woody_the_Viking #800080 19
Woody_the_Viking #800080 20
Woody_the_Viking #800080 22
Aledius          #8B0000 24
Aledius          #8B0000 19
Aledius          #8B0000 20
Aledius          #8B0000 22
Smoothbore247    #228B22 24
Smoothbore247    #228B22 19
Smoothbore247    #228B22 20
Smoothbore247    #228B22 22

所需的输出是:

conan230160      #1E90FF 24
Woody_the_Viking #800080 19
Aledius          #8B0000 20
Smoothbore247    #228B22 22

我不知道是否有人可以指出我正确的方向或解释如何做到这一点？

非常感谢。杰森

编辑:

我不知道我是否已经接近了。

SELECT wot_clan_members.account_id, wot_clan_members.account_name,     wot_info.color, total
FROM wot_info

JOIN wot_clan_members
ON wot_clan_members.account_id = wot_info.account_id

JOIN (SELECT COUNT(*) total FROM wot_info  WHERE wot_info.personnelofficer <> ''     GROUP BY wot_info.personnelofficer ) as total
ON wot_clan_members.account_id = wot_info.account_id

WHERE wot_clan_members.role = 'personnel_officer'
GROUP BY wot_clan_members.account_name

输出:

account_id  account_name     color   total
517440262   Aledius          #8B0000 24
508425497   conan230160      #1E90FF 24
528446417   Smoothbore247    #228B22 24
510048652   Woody_the_Viking #800080 24

它为每个重复第一个 COUNT()

Answer 1

第二个加入需要加入条件。

试试这个:

SELECT wot_clan_members.account_name, wot_info.color, total
FROM wot_info
JOIN wot_clan_members ON wot_clan_members.account_id = wot_info.account_id
JOIN (
    SELECT COUNT(*) total, personnelofficer
    FROM wot_info 
    WHERE wot_info.personnelofficer <> '' 
    GROUP BY wot_info.personnelofficer
) as total ON total.personnelofficer = wot_info.personnelofficer
WHERE wot_clan_members.role = 'personnel_officer'