按DESC排序的SQL命令不工作

Question

select 

customers.id,
customers.Name,
customers.Surname,

(select ifnull(sum(bets.amount),0)
from bets 
where customers.id=bets.customerId
and bets.date >'2014-06-01'
and bets.date <'2014-06-02'
order by bets.amount DESC
) as '1st_June',

(select ifnull(sum(bets.amount),0)
from bets 
where customers.id=bets.customerId
and bets.date >'2014-06-02'
and bets.date <'2014-06-03'
order by bets.amount DESC
) as '1st_June',


from customers

group by customers.id

我需要有一个DESC订单，因为我想限制100，所以我得到前100个值。有人能建议一个办法吗...？

Answer 1

命令不能像那样通过小组。

这应该会给你你想要的东西：

SELECT customers.id
    ,customers.Name
    ,customers.Surname
    ,(
        SELECT ifnull(sum(bets.amount), 0)
        FROM bets
        WHERE customers.id = bets.customerId
            AND bets.date > '2014-06-01'
            AND bets.date < '2014-06-02'
        ) AS First_June
    ,(
        SELECT ifnull(sum(bets.amount), 0)
        FROM bets
        WHERE customers.id = bets.customerId
            AND bets.date > '2014-06-02'
            AND bets.date < '2014-06-03'
        ) AS Second_June
FROM customers
GROUP BY customers.id
ORDER BY First_June DESC
    ,Second_June DESC
LIMIT 100

• 注意：（改为“first_june”。在多个SQL服务器中，以数字开头的列名会出现问题，您没有指定使用的名称。Oracle、MS SQL、MySql、Postgres等）