我在 SQL 表 中有一个列。其中包含交货单的日期。

因此可以重复相同的日期(一天内我们交付了多个订单)，如下所示:

05-01-16
05-01-16
05-01-16
08-01-16
08-01-16
14-01-16
22-01-16
22-01-16
04-02-16
05-02-16
05-02-16

我想，计算每篇文章在每6个月的营业额的AVG，我再解释一下:

From January to June ==> Turnover 1
From Febrary to July ==> Turnover 2
From March to August ==> Turnover 3
From April to September ==> Turnover 4
From May to Obtober ==> Turnover 5
From June to November ==> Turnover 6
From July to December ==> Turnover 7

我已经通过下面的请求提取了月份，但是我无法动态计算(因为我的数据应该每个月都在变化)像上面这个例子那样的营业额:

select distinct extract (month from Article) as mt 
order by mt

我尝试使用游标，但找不到最佳解决方案。

我请求计算前 6 个月中每个客户每篇文章的营业额(我手动完成)如下:

select "LRU", "Client", round(sum("Montant_fac_eur"))
from "foundry"
where "Nature"='Repair' 
and "Client"={{w_widget3.selectedValue}}
and "annee"='2016'
and extract (month from "date") between '1' and '6'


group by "LRU", "Client"

她的结果如下:

 LRU            Client  round
"article1"       4001   8859     Turnover of article1 from January to June
"article2"       4001   94315    Turnover of article2 from January to June
"article3"       4001   273487   Turnover of article3 from January to June
"article4"       4001   22292    Turnover of article4 from January to June
"article5"       4001   22292    Turnover of article5 from January to June
"article6"       4001   42590    Turnover of article6 from January to June
"article7"       4001   9965     Turnover of article7 from January to June
"article8"       4001   39654    Turnover of article8 from January to June
"article9"       4001   3883     Turnover of article9 from January to June
"article10"      4001   41612    Turnover of article10 from January to June

我想做一个循环来计算每 6 个月的营业额，如果可能的话不用手动编写？ 有人可以帮助我并给我一个解决方案或建议我该怎么做吗？ 谢谢。

最佳答案

在这里您可以查看问题的简化定义和解决方案(如果我理解正确的话): http://sqlfiddle.com/#!9/48a2e1/1

CREATE TABLE foundry
(
    lru varchar(50) NOT NULL,
    client int  NOT NULL,
    purchase_date date,
    price int NOT NULL
);

INSERT INTO foundry (lru, client, purchase_date, price) VALUES
("article1", 4001, "01-01-16", 100),
("article1", 4001, "01-01-17", 200),
("article1", 4001, "01-02-16", 300),
("article1", 4001, "01-04-16", 400),
("article1", 4001, "01-06-16", 500),
("article1", 4001, "01-08-16", 600),
("article1", 4001, "01-10-16", 700),
("article1", 4001, "01-11-16", 800),
("article1", 4002, "01-01-16", 900),
("article1", 4002, "01-07-16", 1000),
("article1", 4002, "01-12-16", 1100);

基本上我们有一个包含四列的表:lru(文章名称)、客户、购买日期和一些价格。

解决方案如下:

SELECT lru, client, avg(price), COUNT(*) as total_items,
MONTHNAME(STR_TO_DATE(L, '%m')) as start_month, MONTHNAME(STR_TO_DATE(R, '%m')) as end_month FROM foundry,
(
  SELECT 1 as L, 6 as R
    UNION ALL
  SELECT 2, 7
    UNION ALL
  SELECT 3, 8
    UNION ALL
  SELECT 4, 9
    UNION ALL
  SELECT 5, 10
    UNION ALL
  SELECT 6, 11
    UNION ALL
  SELECT 7, 12
) months
WHERE month(purchase_date) >= L AND month(purchase_date) <= R
GROUP BY lru, client, L, R

思路是:

1. 生成所有可能的月份组合:1-6、2-7、...、7、12
2. 将源数据与生成的月份组合合并
3. 将 AVG 与 GROUP BY 结合使用

结果:

lru     client  avg(price)  total_items     start_month     end_month
article1    4001    300     5   January     June
article1    4001    400     3   February    July
article1    4001    500     3   March   August
article1    4001    500     3   April   September
article1    4001    600     3   May     October
article1    4001    650     4   June    November
article1    4001    700     3   July    December
article1    4002    900     1   January     June
article1    4002    1000    1   February    July
article1    4002    1000    1   March   August
article1    4002    1000    1   April   September
article1    4002    1000    1   May     October
article1    4002    1000    1   June    November
article1    4002    1050    2   July    December

关于mysql - 如何计算每学期的营业额，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45464774/