mysql - 如何在MySQL中使用多个代码来获取like语句的结果？

Question

我有一个表，其中存储上传的文件信息如下。

我编写了以下查询来获取上传结果。

set @v_stk_code = '50400'; -- 2753,8601
set @v_date = '0619';
set @sas = (Select Count(file_name) from ims_data.batchlog where file_name like concat('%',@v_stk_code ,'%',@v_date,'.txt','%') and file_name like 'SAS%' and upload_status = 'success');
set @product = (Select Count(file_name) from ims_data.batchlog where file_name like concat('%',@v_stk_code ,'%',@v_date,'.txt','%') and file_name like 'P%' and upload_status = 'success');
Select @v_stk_code,
    case 
        when @sas=0 and @product=0 then 'SAS and Product not Uploaded' 
        when @sas>0 and @product>0 then 'SAS and Product is Uploaded' 
        when @sas>0 and @product=0 then 'SAS uploaded but Product File Missing' 
        when @sas=0 and @product>0 then 'Product uploaded but SAS File Missing' 
    end as `Status`;

我正在获取一个最常用代码的结果，但我想获取参数中提供的多个最常用结果。

Answer 1

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

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 p 匹配的 sas，如 p111 中 try catch 所有这些类型的好奇心，因为文本消息(在我看来)会很困惑并且更好地制成表格。

+------+-----+----------------+-------------+------------------+---------------+-----------+
| 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 不关心扩展名是什么，只要它存在即可，并且它确实关心有一个 _。