2. (2 points) Please find the dates without any placed order between the time range. (Hint: write a recursive query to create date sequence and use the select statement in Q1 to assign user-defined variables so that you can have a neat recursive query.)
时间: 2024-02-12 22:05:58 浏览: 41
Assuming we have a table `orders` with columns `order_id`, `order_date`, and `customer_id`, we can use the following recursive query to generate a sequence of dates between a specified time range and then find the dates without any placed order:
```
WITH RECURSIVE dates AS (
SELECT DATE('2021-01-01') AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM dates
WHERE date < DATE('2021-12-31')
), orders_count AS (
SELECT order_date, COUNT(*) AS num_orders
FROM orders
WHERE order_date BETWEEN DATE('2021-01-01') AND DATE('2021-12-31')
GROUP BY order_date
)
SELECT dates.date
FROM dates
LEFT JOIN orders_count ON dates.date = orders_count.order_date
WHERE orders_count.num_orders IS NULL;
```
In this query, we first define a recursive CTE called `dates` that generates a sequence of dates between `2021-01-01` and `2021-12-31`. Then, we define another CTE called `orders_count` that counts the number of orders for each date between the same time range.
Finally, we join the `dates` CTE with the `orders_count` CTE using a left join and filter for the rows where `num_orders` is null. These are the dates without any placed order.
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)