sql实现获取某个时间范围内的所有年份,缺失自动补全
时间: 2023-11-18 11:04:13 浏览: 40
可以使用以下 SQL 语句获取某个时间范围内的所有年份:
```sql
WITH RECURSIVE years AS (
SELECT MIN(EXTRACT(YEAR FROM date_column)) AS year
FROM table_name
UNION ALL
SELECT year + 1
FROM years
WHERE year < MAX(EXTRACT(YEAR FROM date_column))
)
SELECT year
FROM years;
```
其中,`date_column` 是包含日期的列名,`table_name` 是表名。
如果某些年份在数据中缺失,可以使用以下 SQL 语句自动补全缺失的年份:
```sql
WITH RECURSIVE years AS (
SELECT MIN(EXTRACT(YEAR FROM date_column)) AS year
FROM table_name
UNION ALL
SELECT year + 1
FROM years
WHERE year < MAX(EXTRACT(YEAR FROM date_column))
)
SELECT year, COALESCE(data_value, 0) AS data_value
FROM years
LEFT JOIN (
SELECT EXTRACT(YEAR FROM date_column) AS year, SUM(data_column) AS data_value
FROM table_name
GROUP BY year
) AS data ON years.year = data.year;
```
其中,`data_column` 是需要统计的列名,`data_value` 是统计结果,如果某个年份在数据中缺失,则对应的 `data_value` 为 0。
相关推荐
![txt](https://img-home.csdnimg.cn/images/20210720083642.png)
![txt](https://img-home.csdnimg.cn/images/20210720083642.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![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)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)