Py学习  »  DATABASE

如何在MySQL中使用多个代码得到like语句的结果?

Shahab Haidar • 6 年前 • 2030 次点击  

enter image description here

我写了下面的查询得到上传结果。

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`;

我得到了一个最粗代码的结果,但我想得到多个最粗的结果,这将在参数中提供。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/57056
文章 [ 1 ]  |  最新文章 6 年前
P.Salmon
Reply   •   1 楼
P.Salmon    7 年前

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

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