P.Salmon

与@akina差不多,但不那么复杂。此处,cte仅用于识别日期差异大于;1然后加入日期表并联合。开始日期、中间日期和结束日期由一个用于订购的标志标识。 这可以在没有cte的情况下完成,但cte确实有助于澄清(我认为), 注:我对样本数据进行了轻微修改

drop table if exists t;
create table t
(timeoff_id int, timeoff_begin date, timeoff_end date);
insert into t values
(      1     , '2020-10-01'    , '2020-10-05'),  
(      2     , '2020-11-15'    , '2020-11-15'),  
(      3     , '2020-12-18'    , '2020-12-21');  

with cte as
(select 2 as beg,timeoff_id , timeoff_begin, timeoff_end ,datediff(timeoff_end, timeoff_begin) diff 
from t
where datediff(timeoff_end, timeoff_begin) > 1)
select cte.beg,cte.timeoff_id,dates.dte
from cte 
join dates where dates.dte > cte.timeoff_begin and dates.dte <= date_add(cte.timeoff_begin, interval cte.diff -1 day) 
union all
(select 1 as beg,timeoff_id , timeoff_begin dt from t 
union all
select 3 ,timeoff_id , timeoff_end from t 
)
order by timeoff_id, beg,dte;

+-----+------------+------------+
| beg | timeoff_id | dte        |
+-----+------------+------------+
|   1 |          1 | 2020-10-01 |
|   2 |          1 | 2020-10-02 |
|   2 |          1 | 2020-10-03 |
|   2 |          1 | 2020-10-04 |
|   3 |          1 | 2020-10-05 |
|   1 |          2 | 2020-11-15 |
|   3 |          2 | 2020-11-15 |
|   1 |          3 | 2020-12-18 |
|   2 |          3 | 2020-12-19 |
|   2 |          3 | 2020-12-20 |
|   3 |          3 | 2020-12-21 |
+-----+------------+------------+

1) 放弃分数>12 2)计算累积总和3)计算出所需范围内的最大累积总和4)选择累积总和为<最大累积金额

DROP table if exists t;

create table t
( Name varchar(10), score int);
insert into t values
('Anton' ,   12),    
('George',   12),    
('John'  ,   1),   
('Piter' ,   1),    
('Tom'   ,   2),
('bilal' ,   5)
;   

with 
cte as
(select t.*,
         sum(score) over (order by name) cumsum
from t
where score < 12) ,
cte1 as
(select max(cumsum) maxcumsum from cte where cumsum < 12 and cumsum > 5),
cte2 as
(select  cte.*,cte1.maxcumsum
from cte
cross join cte1)

select * from cte2 
where maxcumsum > 5 and cumsum <= maxcumsum
order by name;

+-------+-------+--------+-----------+
| Name  | score | cumsum | maxcumsum |
+-------+-------+--------+-----------+
| bilal |     5 |      5 |         9 |
| John  |     1 |      6 |         9 |
| Piter |     1 |      7 |         9 |
| Tom   |     2 |      9 |         9 |
+-------+-------+--------+-----------+
4 rows in set (0.022 sec)

也许可以采用不同的方法,并以表格形式呈现结果。鉴于

drop table if exists t;
create table t
(id int auto_increment primary key, file_name varchar(100),upload_status varchar(10));

insert into t (file_name,upload_status) values
('p333_0709.txt','fail'),
('p111_0809.txt','success'),
('p111_0809.txt','success'),
('sas111_0809.txt','success'),
('p222_0809.txt','success'),
('sas222_0809.txt',null),
('p333_0809.txt','success'),
('sas222_0809.txt','success');

您可以使用子查询a(或链接表)来指定您感兴趣的产品和日期,使用子查询s来拆分文件名,然后可以将文件名与a保持连接。然后主查询可以执行一些条件聚合。

select dt,prd,
         sum(case when concat('p',prd) = fileproduct and upload_status = 'success' then 1 else 0 end) ploadedsuccess,
         sum(case when concat('p',prd) = fileproduct and (upload_status is null or upload_status <> 'success') then 1 else 0 end) ploadedfail,
         sum(case when concat('sas',prd) = fileproduct and upload_status = 'success' then 1 else 0 end) sasloadedsuccess,
         sum(case when concat('sas',prd) = fileproduct and (upload_status is null or upload_status <> 'success') then 1 else 0 end) sasloadedfall,
         sum(case when fileproduct is null then 1 else 0 end) 'notloaded'
from
(
(select 111 as prd, '0809' as dt union select 222, '0809' as dt union select 333, '0709' as dt union select 444, '0809' as dt) a

left join
(
select substring_index(file_name,'_',1) fileproduct,
         cast(substring_index(substring_index(file_name,'.',1) ,'_',-1) as int) fileproductdate,
         upload_status
from t) s
on (concat('p',a.prd) = s.fileproduct or concat('sas',a.prd) = s.fileproduct) and
    s.fileproductdate = a.dt
) 
group by dt,prd

我不知道你的数据,但可能会有2个匹配的sas在p111试图捕捉所有这些类型的好奇,因为短信(在我看来)将是凌乱和更好的表格。

+------+-----+----------------+-------------+------------------+---------------+-----------+
| dt   | prd | ploadedsuccess | ploadedfail | sasloadedsuccess | sasloadedfall | notloaded |
+------+-----+----------------+-------------+------------------+---------------+-----------+
| 0709 | 333 |              0 |           1 |                0 |             0 |         0 |
| 0809 | 111 |              2 |           0 |                1 |             0 |         0 |
| 0809 | 222 |              1 |           0 |                1 |             1 |         0 |
| 0809 | 444 |              0 |           0 |                0 |             0 |         1 |
+------+-----+----------------+-------------+------------------+---------------+-----------+
4 rows in set (0.00 sec)

注意这里有一些假设,例如文件名有一个标准格式-mycode不关心扩展名是什么,只要它在那里,它确实关心有一个。

因为prod_info没有on子句。

@d1始终为空,因为csv文件中只有3列。我想你是想把csv文件中的第二列指定给@d1。

DROP TABLE IF EXISTS T;
CREATE TABLE T (sno INT,sdate DATETIME,orderno varchar(20));

LOAD DATA INFILE 'C:\\Program Files\\MariaDB 10.1\\data\\sandbox\\data.txt'
INTO TABLE t
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(sno, @d1, orderno) 
SET sdate = date_format(str_to_date(@d1, '%d.%m.%y'), '%Y-%m-%d');
;

select * from t;

+------+---------------------+-------------+
| sno  | sdate               | orderno     |
+------+---------------------+-------------+
 |   1 | 2013-03-01 00:00:00 | 6310086579
 |   2 | 2013-03-01 00:00:00 | 6310086569
 |   3 | 2013-03-01 00:00:00 | 6310086573
 |   4 | 2013-03-04 00:00:00 | 6310086592
 |   5 | 2013-03-04 00:00:00 | 6310086592
 |   6 | 2013-03-04 00:00:00 | 6310086592
 |   7 | 2013-03-05 00:00:00 | 6310086633
|    8 | 2013-03-05 00:00:00 | 6310086639  |
+------+---------------------+-------------+
8 rows in set (0.00 sec)

但我认为您根本不需要转换第2列。你应该复习一下 https://dev.mysql.com/doc/refman/8.0/en/load-data.html 特别是以“set子句中的用户变量可以以多种方式使用”开头的部分

2个问题,1-你有一个多余的开始,你应该删除,如果应该是其他2。

DROP function IF EXISTS CalcDoseCount;
DELIMITER $$
create FUNCTION CalcDoseCount (DivType  Int, DivDose  varchar(300), DurationType  float, Duration  float, EveryType  float, Every float,OrderType int)
RETURNS Int   
BEGIN
        Declare SingleDose  int;
        Declare TotalDuration  int;
        Declare DivEvery  int;
        Declare DoseCount  float;

        set TotalDuration = Duration * case DurationType when 0 then 1 when 1 then 7 when 2 then 30 end;

        #begin
        if DivType in (0,2,3) then
            if EveryType = 0  then -- every type is hours 
                set DoseCount = CEILING( TotalDuration * 24 / Every);
            elseif EveryType = 1 then-- every type is days
                set DoseCount =  CEILING(TotalDuration / Every );
            elseif EveryType = 2 then-- every type is weeks
                set DoseCount =  CEILING(TotalDuration / ( Every * 7));
            elseif EveryType = 3 then-- every type is times
                set DoseCount =  TotalDuration * Every;
            elseif EveryType = 4 then-- every type is times per week
                set DoseCount =  CEILING( TotalDuration / 7 ) * Every;
            end if;
        end if;
        If DivType = 1 then
            set DivEvery = right(DivDose,1);
            if  DivEvery = 0 then  
                set DivEvery = 1;
            end if;
            set DoseCount =  (CEILING(TotalDuration / DivEvery));
        end if;
        if isnull(DoseCount) then
            set DoseCount = 1;
        end if;
        if DoseCount = 0 then 
            set DoseCount = 1;
        end if;
        return DoseCount ;         
END $$
DELIMITER ;

对于在这个查询中查找的任何其他人来说,例如给出了两个行号的2行

+----------+-------+
| sudentid | fname |
+----------+-------+
|      101 | NULL  |
|      103 | NULL  |
|      112 | NULL  |
+----------+-------+
3 rows in set (0.00 sec)

查询产生

+----------+-------+--------+
| sudentid | fname | rownum |
+----------+-------+--------+
|      101 | NULL  |      1 |
|      103 | NULL  |      2 |
+----------+-------+--------+
2 rows in set (0.00 sec)

有一个解释计划

+------+-------------+---------+------+---------------+------+---------+------+------+----------------------------------------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+------+-------------+---------+------+---------------+------+---------+------+------+----------------------------------------------+
|    1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where; Using temporary; Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

解释计划符合我的期望,即订购时间几乎是最后一天。( https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ )

但似乎发生的是,由于ORDER BY递增了select中使用的变量,因此还有另一个WHERE子句以事件的实际顺序调用,而这一顺序并未反映在explain计划中(我认为这反映了事件的逻辑顺序)。

我不知道OP在这里要做什么(或者如果结果不正确),但这不是通常在版本8之前分配行号的方式。