mysql动态组合查询

Question

mysql中如何根据下述查询最终得到每个日期对应一个结果？当前Q2可以得到指定日期的值。若是修改为动态的日期列，该如何修改？
report_date TTL output close_rate
2024-2-3 1643 1527 92.94
2024-2-2 1600 1439 89.94
2024-2-1 1324 1300 98.19
2023-1-31 1762 1721 97.67
2023-1-30 1378 1299 94.27

//Q1得到日期列
select date（operation_time）as report_date from information where operation_time >'2023-01-01' group by report_date 
```sql

```sql
//Q2中的‘2024-02-03’如何根据Q1的动态日期列的每个日期进行计算
SELECT '2024-02-03' as report_date,count(*) as TTL,
count(case when new_status IN('3','4','7') then id end)'output',
count(case when new_status IN('3','4','7') then id end) * 100 / count(*) as close_rate 
 FROM (
SELECT *  
FROM (  
    SELECT id,operation_time, sn, status  
    FROM information
    WHERE date(operation_time) = DATE_SUB('2024-02-03', INTERVAL 2 DAY) and status ='1'
) AS query1  
LEFT JOIN (  
    SELECT operation_time as new_date, sn as new_sn, status as new_status  
    FROM information
    WHERE operation_time between DATE_SUB('2024-02-03', INTERVAL 48 HOUR) AND '2024-02-03'  and status in ('3','4','7') 
) AS query2  
ON query1.sn = query2.new_sn)NEW_table

Answer 1

引自免费微信小程序：皆我百晓生

为了动态地根据查询Q1的结果为每个日期生成一个报告，我们需要将Q2中的硬编码日期替换为变量，并结合子查询或JOIN来关联Q1的日期结果。以下是实现这个需求的一种方式：

-- 首先创建一个临时表（或视图）存储Q1查询结果，便于后续引用
CREATE TEMPORARY TABLE IF NOT EXISTS report_dates AS
SELECT DATE(operation_time) as report_date 
FROM information 
WHERE operation_time > '2023-01-01' 
GROUP BY report_date;

-- 然后使用循环或者内连接的方式来遍历每个日期并执行Q2类型的查询
SELECT r.report_date, TTL, output, close_rate
FROM (
    SELECT report_date,
           COUNT(*) as TTL,
           COUNT(CASE WHEN new_status IN ('3', '4', '7') THEN id END) AS 'output',
           COUNT(CASE WHEN new_status IN ('3', '4', '7') THEN id END) * 100 / COUNT(*) AS close_rate
    FROM (
        SELECT query1.id, query1.operation_time, query1.sn, query1.status
        FROM (
            SELECT id, operation_time, sn, status
            FROM information
            WHERE DATE(operation_time) = DATE_SUB(r.report_date, INTERVAL 2 DAY)
              AND status = '1'
        ) AS query1
        LEFT JOIN (
            SELECT operation_time AS new_date, sn AS new_sn, status AS new_status
            FROM information
            WHERE operation_time BETWEEN DATE_SUB(r.report_date, INTERVAL 48 HOUR) AND r.report_date
              AND status IN ('3', '4', '7')
        ) AS query2 ON query1.sn = query2.new_sn
    ) AS NEW_table
    INNER JOIN report_dates AS r ON DATE(NEW_table.operation_time) = r.report_date
) AS result_table;

然而，在MySQL中直接进行循环并不方便，上述代码尝试通过内连接的方式一次性获取所有日期对应的数据。但请注意，MySQL不支持直接在SQL查询中使用循环结构。如果你确实需要在MySQL中进行类似循环操作，可能需要编写存储过程来动态构建和执行SQL语句。

另一种替代方案是使用应用层逻辑（如Python、Java等）读取Q1的日期列表，然后对每个日期动态构造并执行Q2类型的查询语句。