mysql查询嵌套查询双重排序

Question

我使用像这样的简单sql查询:$sql="SELECT name, DATE_FORMAT(date_add,'%d-%m-%Y') as mydate, payment FROM atable"我得到示例中的 3 列表:

name  mydate     payment
luke  10-12-2015  50
tom   13-12-2015  60
john  13-12-2015  40
tom   14-12-2015  30
eva   15-12-2015  40
john  16-12-2015  70
tom   16-12-2015  20

是否可以像这样编写sql查询(我猜可能是嵌套的)，首先我会按名称获取每个组的付款总额，然后按总和对其进行DESC排序，但最终的表将如下所示:

name  mydate     payment
john  13-12-2015  40
john  16-12-2015  70
tom   13-12-2015  60
tom   14-12-2015  30
tom   16-12-2015  20
luke  10-12-2015  50
eva   15-12-2015  40

约翰排在第一位，因为他的付款总额是 110，与汤姆的相同，但按字母顺序，他排在第一位，然后是卢克和伊娃。查询会是什么样子(如果可能的话，在一个 sql 查询中完成所有步骤)？

Answer 1

这里有一个方法:SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE atable
    (`name` varchar(4), `mydate` datetime, `payment` int)
;

INSERT INTO atable
    (`name`, `mydate`, `payment`)
VALUES
    ('luke', '2015-12-10 00:00:00', 50),
    ('tom', '2015-12-13 00:00:00', 60),
    ('john', '2015-12-13 00:00:00', 40),
    ('tom', '2015-12-14 00:00:00', 30),
    ('eva', '2015-12-15 00:00:00', 40),
    ('john', '2015-12-16 00:00:00', 70),
    ('tom', '2015-12-16 00:00:00', 20)
;

查询 1:

select
      atable.name
    , atable.mydate
    , atable.payment
    , g.sum_payment
from atable
inner join (
            select name, sum(payment) as sum_payment
            from atable
            group by name
           ) g on atable.name = g.name
order by
      g.sum_payment DESC
    , atable.name
    , atable.mydate
    , atable.payment

Results :

| name |                     mydate | payment | sum_payment |
|------|----------------------------|---------|-------------|
| john | December, 13 2015 00:00:00 |      40 |         110 |
| john | December, 16 2015 00:00:00 |      70 |         110 |
|  tom | December, 13 2015 00:00:00 |      60 |         110 |
|  tom | December, 14 2015 00:00:00 |      30 |         110 |
|  tom | December, 16 2015 00:00:00 |      20 |         110 |
| luke | December, 10 2015 00:00:00 |      50 |          50 |
|  eva | December, 15 2015 00:00:00 |      40 |          40 |