社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
私信  •  关注

P.Salmon

P.Salmon 最近创建的主题
P.Salmon 最近回复了
4 年前
回复了 P.Salmon 创建的主题 » MySQL或MariaDB将单行中的日期范围扩展为多个系列行

与@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 |
+-----+------------+------------+
4 年前
回复了 P.Salmon 创建的主题 » 如何在MySQL中编写两个值之间的循环?

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)
7 年前
回复了 P.Salmon 创建的主题 » 如何在MySQL中使用多个代码得到like语句的结果?

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

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不关心扩展名是什么,只要它在那里,它确实关心有一个。

6 年前
回复了 P.Salmon 创建的主题 » mysql给出了一个语法错误,有许多连接[关闭]

因为prod_info没有on子句。

8 年前
回复了 P.Salmon 创建的主题 » 用diff日期格式将csv上传到mysql

@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子句中的用户变量可以以多种方式使用”开头的部分

7 年前
回复了 P.Salmon 创建的主题 » mysql语法检查

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 ;
8 年前
回复了 P.Salmon 创建的主题 » MySQL中的分配顺序是什么?

对于在这个查询中查找的任何其他人来说,例如给出了两个行号的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之前分配行号的方式。