时间戳

首页 > 解决方案 > 在 mysql 中以 Sum 开头

mysql - 在 mysql 中以 Sum 开头

问题描述

这是数据库的架构

CREATE TABLE `visita_prodotto`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `prezzo` int(15) UNSIGNED NULL DEFAULT NULL COMMENT 'price',
  `id_visita` int(10) UNSIGNED NOT NULL COMMENT 'visit id');


  CREATE TABLE `visita`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_campagna` int(10) UNSIGNED NOT NULL COMMENT 'project',
  `dataorainizio` datetime(0) NOT NULL COMMENT 'start date');

INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (53, 8, 5000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (54, 8, 8000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (55, 9, 4000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (56, 9, 3000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (66, 11, 5544);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (71, 12, 7500);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (77, 13, 5433);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (85, 17, 7200);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (89, 15, 4500);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (94, 16, 3200);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (99, 16, 5800);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (104, 16, 99999);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (107, 22, 7500);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (111, 22, 129999);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (113, 22, 99999);

INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (8, '2020-02-12 15:23:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (9, '2020-02-14 01:59:45', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (11, '2020-02-19 14:27:17', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (12, '2020-02-19 15:43:42', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (13, '2020-02-20 14:02:35', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (15, '2020-02-25 15:58:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (16, '2020-02-21 15:59:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (17, '2020-02-21 16:13:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (22, '2020-03-12 15:11:00', 5);

我有这个查询

 SELECT YEAR(v.dataorainizio) AS Year, WEEK(v.dataorainizio) AS Week, ROUND(sum(prezzo)/1000,2) as total_price
FROM visita_prodotto vp 
join visita v on vp.id_visita=v.id
where YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) and  YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 and v.id_campagna=5
GROUP BY Year, Week
order by Week desc

产生这个结果

YEAR    WEEK TOTAL_PRICE
2020    10   237,50
2020    8    4,50
2020    7    134,68
2020    6    20,00

我想拥有

YEAR    WEEK TOTAL_PRICE DIFFERENCE
2020    10   237,50        233
2020    8    4,50          -130,18
2020    7    134,68        114,68
2020    6    20,00

我尝试过 sum(lead)，但没有成功..

相关表结构

第一张桌子 visita_prodotto

 id      id_visita                 prezzo
 int pk  int(related to visita)    int

加入表访问：

id     dataorainizio id_campagna
int pk datetime      int

我正在使用 MARIADB 10.2

谁能帮我？

谢谢你

标签： mysql

解决方案

当然。您将需要一个子查询，例如

SELECT YEAR(v.dataorainizio) AS Year, WEEK(v.dataorainizio) AS Week, ROUND(sum(prezzo)/1000,2) as total_price, t2.tp - ROUND(sum(prezzo)/1000,2) 
FROM visita_prodotto vp 
join visita v on vp.id_visita=v.id
join (
    SELECT ROUND(sum(prezzo)/1000,2) as tp
    FROM visita_prodotto vp2 
    join visita v2 on vp2.id_visita=v2.id
    where YEARWEEK(v2.dataorainizio,1)<=YEARWEEK(NOW(),1) and  YEARWEEK(v2.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 and v2.id_campagna=5
    GROUP BY YEAR(v2.dataorainizio), WEEK(v2.dataorainizio)
    HAVING (YEAR(v.dataorainizio) > YEAR(v2.dataorainizio)) OR ((YEAR(v.dataorainizio) = YEAR(v2.dataorainizio)) AND (WEEK(v.dataorainizio) > WEEK(v2.dataorainizio)))
    ORDER BY YEAR(v2.dataorainizio) desc, WEEK(v2.dataorainizio) desc
    LIMIT 0, 1
) t2
where YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) and  YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 and v.id_campagna=5
GROUP BY Year, Week, t2.tp
order by Week desc

子查询选择一个组，比当前组早，然后进行减法。我没有数据库示例。如果您仍在苦苦挣扎，请创建一个 SQL Fiddle 并在评论部分分享链接。

推荐阅读