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