好的，以下是建表语句和查询语句:
建表语句：
```sql
CREATE TABLE sales (
id INT PRIMARY KEY,
year INT,
month INT,
sale_money INT
);
```
插入数据语句：
```sql
INSERT INTO sales (id, year, month, sale_money)
VALUES
(1, 2019, 10, 1279),
(2, 2019, 11, 2316),
(3, 2019, 12, 2090),
(4, 2020, 01, 1086),
(5, 2020, 02, 2046),
(6, 2020, 03, 0),
(7, 2020, 04, 2959),
(8, 2020, 05, 1314),
(9, 2020, 06, 2751),
(10, 2020, 07, 1492),
(11, 2020, 08, 1414),
(12, 2020, 09, 2895),
(13, 2020, 10, 2999),
(14, 2020, 11, 1982),
(15, 2020, 12, 2793),
(16, 2021, 01, 2156),
(17, 2021, 02, 1733),
(18, 2021, 03, 2184);
```
查询语句：
```sql
SELECT
s1.year,
s1.month,
s1.sale_money,
ROUND((s1.sale_money - s2.sale_money) / s2.sale_money, 2) AS ratio
FROM
sales s1
LEFT JOIN sales s2 ON s1.year = s2.year AND s1.month = s2.month + 1
WHERE
(s1.year = (SELECT MAX(year) FROM sales) AND s1.month <= (SELECT MAX(month) FROM sales WHERE year = (SELECT MAX(year) FROM sales)))
OR s1.year = (SELECT MAX(year) FROM sales) - 1
ORDER BY
s1.year, s1.month;
```
查询结果：
```
| year | month | sale_money | ratio |
|------|-------|------------|-------|
| 2020 | 01 | 1086 | NULL |
| 2020 | 02 | 2046 | 0.88 |
| 2020 | 03 | 0 | -1.00 |
| 2020 | 04 | 2959 | NULL |
| 2020 | 05 | 1314 | -0.56 |
| 2020 | 06 | 2751 | 1.09 |
| 2020 | 07 | 1492 | -0.46 |
| 2020 | 08 | 1414 | -0.05 |
| 2020 | 09 | 2895 | 1.04 |
| 2020 | 10 | 2999 | 0.04 |
| 2020 | 11 | 1982 | -0.34 |
| 2020 | 12 | 2793 | 0.41 |
| 2021 | 01 | 2156 | -0.23 |
| 2021 | 02 | 1733 | -0.20 |
| 2021 | 03 | 2184 | 0.26 |
```
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
