mysql - 2013 年按 WEEK() 分组故障

Question

我不确定到底发生了什么。

我有一个查询，它获取一周内的所有销售额，但出现轻微故障，将一周分解为 2，这是查询...

SELECT 
    ROUND(SUM(sell_price), 2) AS sell_price, 
    ROUND(SUM(buy_price/100*20), 2) AS fees, 
    ROUND(SUM(buy_price), 2) AS buy_price, 
    ROUND(SUM(sell_price/100*80 - buy_price), 2) AS margin, 
    time 
    FROM order_items 
    GROUP BY WEEK(FROM_UNIXTIME(time), 1) 
    ORDER BY WEEK(FROM_UNIXTIME(time), 1) DESC

输出如下

Sell Price      Fees    Buy Price   Margin  Date
44.01           4.46    22.30       12.91   31-12-2012
464.00          46.58   232.90      138.36  27-12-2012
313.82          31.80   159.01      92.06   10-12-2012
669.91          62.13   310.67      225.26  03-12-2012
265.88          25.57   127.84      84.87   02-12-2012
208.58          15.78   78.88       87.98   24-11-2012
176.90          16.32   81.59       59.93   01-01-2013

从表中可以看出，从 2012 年 12 月 31 日开始的那一周从 2013 年的销售额中扣除，销售额也没有按降序返回，因为 2013 年的销售额应该显示在表的顶部。

返回的表应该类似于...

Sell Price      Fees    Buy Price   Margin  Date
220.2           20.78   103.90      72.84   31-12-2012
464.00          46.58   232.90      138.36  27-12-2012
313.82          31.80   159.01      92.06   10-12-2012
669.91          62.13   310.67      225.26  03-12-2012
265.88          25.57   127.84      84.87   02-12-2012
208.58          15.78   78.88       87.98   24-11-2012

Answer 1

也许这对你有用:

SELECT 
    ROUND(SUM(sell_price), 2) AS sell_price, 
    ROUND(SUM(buy_price/100*20), 2) AS fees, 
    ROUND(SUM(buy_price), 2) AS buy_price, 
    ROUND(SUM(sell_price/100*80 - buy_price), 2) AS margin, 
    DATE_SUB(FROM_UNIXTIME(time), INTERVAL (DAYOFWEEK(FROM_UNIXTIME(time)) - 1) DAY) AS fdow 
    FROM order_items 
    GROUP BY fdow
    ORDER BY fdow DESC