mysql - 按年按月按月生成统计

Question

上次我使用间隔，但我不认为它像我想要的那样工作， 我只想声明年份和月份。任何大师都可以在这里教我吗？

DECLARE @YEAR AS INT = '2015',
 @MONTH AS INT = '1',
  @YEARTO AS INT = '2015',
   @MONTHTO AS INT = '1'


SELECT TYPE_FOOD, COUNT(CASE WHEN STATUS = 'BUY' THEN 1 END) AS [INCOMING], COUNT(CASE WHEN STATUS= 'SELL' THEN 1 END) AS [OUTGOING],DATEDIFF(MONTH, DATE_OF_PURCHASE, @YEAR ) AS [YEAR]  
FROM Shop
GROUP BY TYPE_FOOD, DATEDIFF(MONTH,DATE_OF_PURCHASE, @YEAR )

我当前的查询..

Food TYPE | MONTH | BUY | SELL 
 pizza       12     10     5       
 pizza       13      3     1       
 ....        ...     ..     ..     
 pizza       32     5      2        

如何制作这样的sql:

Food TYPE | MONTH | BUY | SELL | CLEARANCE RATE %
 pizza       Jan-15     10    5        50%
 pizza       Feb-15     3     1        25%
 ....        ...     ..     ..      ..
 pizza       Dec-15     5      2        35%

我也可以在 sql 中做清关报告吗？

Answer 1

使用 date_format() 函数从购买日期列中获取月份和年份:

SELECT TYPE_FOOD, COUNT(CASE WHEN STATUS = 'BUY' THEN 1 END) AS INCOMING, COUNT(CASE WHEN STATUS= 'SELL' THEN 1 END) AS OUTGOING,DATE_FORMAT(DATE_OF_PURCHASE, '%b-%Y') AS YEAR, COUNT(CASE WHEN STATUS = 'BUY' THEN 1 END) * 100/COUNT(CASE WHEN STATUS= 'SELL' THEN 1 END) AS clearance_rate  
FROM Shop
GROUP BY TYPE_FOOD, DATE_FORMAT(DATE_OF_PURCHASE,'%b-%Y' )