php - 合并两个查询，列的总和和最大值

Question

我想将大约3个MySQL查询合并为一个，但有些复杂。 (下面是我的测试查询和表数据示例)

表1:帐户

id  |   account | email  | refer
11  |   aasdasd | 5@as.a | 0
12  |   gasd    | 4@ds.a | 11
13  |   xcsxs   | 3@ss.a | 11
14  |   cbasd   | 2@as.a | 11
15  |   asdv    | 1@gs.a | 11
16  |   sdfgx   | 6@hs.a | 8

...

表2:字符

guid    |account| name | rank   | time
561     |   11  | asda | 945    | 12
562     |   11  | asda | 746    | 19
563     |   11  | asda | 452    | 1
564     |   12  | asda | 123    | 15
565     |   12  | asda | 456    | 18
566     |   13  | asda | 123    | 6
567     |   13  | asda | 789    | 18
568     |   13  | asda | 123    | 17
569     |   15  | asda | 456    | 13
570     |   16  | asda | 123    | 15
571     |   17  | asda | 456    | 16

...
我测试的查询:

 SELECT id FROM accounts WHERE refer='11'

要识别帐户，请引用值“11”第二个查询显示帐户字符的总时间并对其进行求和，其中引用值为 11 :

SELECT a.account, a.email, SUM(c.time) , c.rank
FROM accounts a, characters c
WHERE a.id=c.account
ORDER BY MAX(c.rank) DESC

我想要什么:
我想将上述两个查询二合一并得到如下结果:
排名列是帐户中角色的最高排名，总时间是该帐户的所有角色时间的总和

Account | Email | Rank | TotalTime
gasd    |4@ds.a | 456  | 33
xcsxs   |3@ss.a | 789  | 41
cbasd   |2@as.a |  0   | 0
asdv    |1@gs.a | 456  | 13

第二个 SUM 查询我认为有问题，我现在添加它。

Answer 1

试试这个

    SELECT a.account, a.email,  c.rank,SUM(c.time) totaltime
    FROM accounts a
    INNER JOIN characters c
    ON a.id = c.account

    WHERE a.refer=11
    ORDER BY MAX(c.rank) DESC

编辑:

   SELECT a.account, a.email,  MAX(c.rank) rank,SUM(c.time) totaltime
    FROM accounts a
     left JOIN characters c
    ON a.id = c.account

    WHERE a.refer=11
  group by a.account
   ORDER BY MAX(c.rank) DESC

LOOK DEMO SQLFIDDLE