我正在嘗試比較日期之間銷售資料的增長率。
模式（SQLite v3.39）：
CREATE TABLE "VENTAS" (
"date" TEXT,
"code" TEXT,
"qty" REAL,
"cost" REAL,
"price" REAL
);
INSERT INTO "VENTAS" VALUES
("2022-01-01","MARIO", 1, 1.00, 2.00),
("2022-01-05","MARIO", -1, -1.00, -2.00),
("2022-01-09","LUIGI", 1, 1.00, 2.00),
("2022-01-23","LUIGI", 1, 1.00, 2.00),
("2022-01-30","PEACH", -1, -1.00, -2.00),
("2022-02-01","MARIO", 1, 1.00, 2.00),
("2022-02-11","MARIO", -1, -1.00, -2.00),
("2022-02-19","LUIGI", 1, 1.00, 2.00),
("2022-02-28","LUIGI", 1, 1.00, 2.00),
("2022-03-01","PEACH", -1, -1.00, -2.00),
("2022-03-15","MARIO", 1, 1.00, 2.00),
("2022-03-20","MARIO", -1, -1.00, -2.00),
("2022-03-29","LUIGI", 1, 1.00, 2.00),
("2022-04-09","LUIGI", 1, 1.00, 2.00),
("2022-04-12","PEACH", -1, -1.00, -2.00),
("2022-04-18","MARIO", 1, 1.00, 2.00),
("2022-04-22","MARIO", -1, -1.00, -2.00),
("2022-04-22","LUIGI", 1, 1.00, 2.00),
("2022-05-13","LUIGI", 1, 1.00, 2.00),
("2022-05-25","PEACH", -1, -1.00, -2.00);
SELECT code,
(SELECT SUM(qty) WHERE date BETWEEN '2022-01-01' AND '2022-01-31') as qty,
(SELECT SUM(qty) WHERE date BETWEEN '2022-02-01' AND '2022-02-28') as qty2,
(SELECT SUM((price * ABS(qty))) WHERE date BETWEEN '2022-01-01' AND '2022-01-31') as sale,
(SELECT SUM((price * ABS(qty))) WHERE date BETWEEN '2022-02-01' AND '2022-02-28') as sale2
FROM VENTAS
WHERE qty != 0
GROUP BY code;
結果：
| 代碼 | 數量 | 數量2 | 銷售 | 銷售2 |
|---|---|---|---|---|
| 路易吉 | 8個 | 16 | ||
| 馬里奧 | 0 | 0 | ||
| 桃 | -4 | -8 |
資料庫小提琴
我預計：
| 代碼 | 數量 | 數量2 | 銷售 | 銷售2 |
|---|---|---|---|---|
| 路易吉 | 2個 | 2個 | 4.00 | 4.00 |
| 馬里奧 | 0 | 0 | 0 | 0 |
| 桃 | -1 | 0 | -2.00 | 0 |
它使用整個總和而不是符合 where 子句。
uj5u.com熱心網友回復：
使用條件聚合：
SELECT code,
TOTAL(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-31' THEN qty END) AS qty,
TOTAL(CASE WHEN date BETWEEN '2022-02-01' AND '2022-02-28' THEN qty END) AS qty2,
TOTAL(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-31' THEN price * ABS(qty) END) AS sale,
TOTAL(CASE WHEN date BETWEEN '2022-02-01' AND '2022-02-28' THEN price * ABS(qty) END) AS sale2
FROM VENTAS
WHERE qty <> 0
GROUP BY code;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/536622.html
標籤：数据库sqlite
上一篇：如何從結果集中洗掉每第n行？