时间戳

首页 > 解决方案 > Excel转Mysql查询

mysql - Excel转Mysql查询

问题描述

需要帮助将 excel 公式转换为 MySQL 查询

这是问题=（SUM（D2：D5189））/（SUM（C2：C5189））。

非常感谢您提前。在此处输入图像描述

标签： mysqlexcel

解决方案

给定

drop table if exists t;
create table t(dt date,cola int,colb int);
insert into t values
(str_to_date('29/10/2020','%d/%m/%Y'),147,150),
(str_to_date('30/10/2020','%d/%m/%Y'),160,170),
(str_to_date('01/11/2020','%d/%m/%Y'),170,170),
(str_to_date('02/11/2020','%d/%m/%Y'),180,170),
(str_to_date('03/11/2020','%d/%m/%Y'),190,170);

您可以使用相关子查询来获取从当前行到列末尾的总计

select t.*,
         (select sum(cola) from t t1 where t1.dt >=t.dt) sumcola,
         (select sum(colb) from t t1 where t1.dt >=t.dt) sumcolb,
         (select sum(colb) from t t1 where t1.dt >=t.dt) /
         (select sum(cola) from t t1 where t1.dt >=t.dt) kpi
from t;

或者如果您有版本 8 或更高版本

select t.*,
         sum(cola) over (order by dt rows between current row and unbounded following) sumcola,
         sum(colb) over (order by dt rows between current row and unbounded following) sumcolb,
         sum(colb) over (order by dt rows between current row and unbounded following) /
         sum(cola) over (order by dt rows between current row and unbounded following) kpi;
from t;

+------------+------+------+---------+---------+--------+
| dt         | cola | colb | sumcola | sumcolb | kpi    |
+------------+------+------+---------+---------+--------+
| 2020-10-29 |  147 |  150 |     847 |     830 | 0.9799 |
| 2020-10-30 |  160 |  170 |     700 |     680 | 0.9714 |
| 2020-11-01 |  170 |  170 |     540 |     510 | 0.9444 |
| 2020-11-02 |  180 |  170 |     370 |     340 | 0.9189 |
| 2020-11-03 |  190 |  170 |     190 |     170 | 0.8947 |
+------------+------+------+---------+---------+--------+
5 rows in set (0.001 sec)

推荐阅读