展示从mysql数据库每日文件加载的状态

Question

我正在处理一个项目，我们每天会收到一些文件，这些文件会被加载到数据库中，并将它们的状态记录在元数据中。

例如， 我们有两个不同的客户，从他们那里获取三种不同类型的文件，type_a，type_b和type_c。

CLIENT_MASTER
----------------------
client_id|client_name
1|xxx
2|yyy

文件状态存储在FILE_MASTER表中。

FILE_MASTER
-----------------------------------
file_key|client_id|filename|status
1|1|type_a_2010-10-07.csv|12
2|1|type_b_2010-10-07.csv|12
3|1|type_c_2010-10-07.csv|12
4|2|type_a_2010-10-07.csv|12
5|2|type_b_2010-10-07.csv|12

状态键存储在STATUS_MASTER表中。

STATUS_MASTER
-------------------
status_key|status
12|Completed

我希望开发一个仪表盘，显示每日文件加载的状态，格式如下 -

Client|type_a|type_b|type_c
xxx|Yes|Yes|Yes
yyy|Yes|Yes|No

如果能得到帮助，将不胜感激。

- Kamlesh Gallani

Answer 1

这个查询会返回当前日期的结果：

SELECT cm.client_name,
       CASE WHEN v.type_a_count > 0 THEN 'Yes' ELSE 'No' END AS type_a,
       CASE WHEN v.type_b_count > 0 THEN 'Yes' ELSE 'No' END AS type_b,
       CASE WHEN v.type_c_count > 0 THEN 'Yes' ELSE 'No' END AS type_c
FROM client_master cm
     LEFT OUTER JOIN (
        SELECT fm.client_id,
               SUBSTRING(fm.client_name, 8, 10) AS file_date,
               SUM(CASE WHEN SUBSTRING(fm.client_name, 1, 6) = 'type_a' THEN 1 ELSE 0 END) AS type_a_count,
               SUM(CASE WHEN SUBSTRING(fm.client_name, 1, 6) = 'type_b' THEN 1 ELSE 0 END) AS type_b_count,
               SUM(CASE WHEN SUBSTRING(fm.client_name, 1, 6) = 'type_c' THEN 1 ELSE 0 END) AS type_c_count 
        FROM file_master fm
        WHERE SUBSTRING(fm.client_name, 8, 10) = CURDATE()
          AND fm.status = 12
        GROUP BY fm.client_id, SUBSTRING(fm.client_name, 8, 10)
     ) v ON cm.client_id = v.client_id

- Kirill Kin

---

谢谢 @Kirill，这几乎达到了预期的效果，但一个不理想的情况是当某个客户没有文件时，它将不会显示该客户的记录。例如，对于客户xxx，今天没有文件，它只显示一行 - yyy|Yes|Yes|No，但应该显示2行，即xxx|No|No|No和yyy|Yes|Yes|No。 - Kamlesh Gallani

你能帮我一下吗？ - Kamlesh Gallani

我已经更新了这个问题的解决方案。请尝试一下。 - Kirill Kin

非常感谢。运行得很好。 - Kamlesh Gallani

Answer 2

尝试这个查询

SELECT c.name,
IF(FIND_IN_SET('type_a', GROUP_CONCAT(SUBSTRING_INDEX(f1.filename,'_',2))), 'Yes', 'No') as type_a,
IF(FIND_IN_SET('type_b', GROUP_CONCAT(SUBSTRING_INDEX(f1.filename,'_',2))), 'Yes', 'No') as type_b,
IF(FIND_IN_SET('type_c', GROUP_CONCAT(SUBSTRING_INDEX(f1.filename,'_',2))), 'Yes', 'No') as type_c
FROM client_master c
JOIN file_master f1 ON(f1.client_id = c.client_id)
JOIN status_master sm ON(sm.status = f1.status)
WHERE sm.status='Completed' AND SUBSTRING_INDEX(f1.filename,'_',-1) = CURDATE()
GROUP BY c.id

- Arun Krish