DB-小提琴
CREATE TABLE logistics (
id SERIAL PRIMARY KEY,
time_stamp DATE,
quantity INT
);
INSERT INTO logistics
(time_stamp, quantity)
VALUES
('2020-01-14', '100'),
('2020-01-15', '400'),
('2020-01-16', '530'),
('2020-01-17', '500');
預期結果
time_stamp | quantity |
------------|------------|------
2020-01-15 | 400 |
2020-01-16 | 530 |
2020-01-17 | 500 |
在結果中，我想顯示除第一行之外的quantity所有日期。
我知道在上面的例子中我可以用這樣的簡單方法做到這一點：WHERE-condition
SELECT
l.time_stamp AS time_stamp,
SUM(quantity) AS quantity
FROM logistics l
WHERE time_stamp <> '2020-01-14'
GROUP BY 1
ORDER BY 1;
但是，既不能保證
第一個time_stamp永遠是2020-01-14
，也不保證MIN(2020-01-14)永遠在第一行。
因此，我想知道如何修改查詢以始終不顯示結果的第一行？
uj5u.com熱心網友回復：
您可以將 WHERE 條件與子查詢一起使用
SELECT l.time_stamp,
SUM(quantity) AS quantity
FROM logistics l
WHERE time_stamp > (select min(time_stamp) from logistics)
GROUP BY 1
ORDER BY 1;
uj5u.com熱心網友回復：
您可以使用 LIMIT OFFSET子句，例如：
SELECT
l.time_stamp AS time_stamp,
SUM(quantity) AS quantity
FROM logistics l
GROUP BY 1
ORDER BY 1
LIMIT all OFFSET 1
;
uj5u.com熱心網友回復：
由于這是 Postgresql，您可以使用 OFFSET 子句。
https://www.postgresql.org/docs/current/queries-limit.html
SELECT
l.time_stamp AS time_stamp,
SUM(quantity) AS quantity
FROM logistics l
GROUP BY 1
ORDER BY 1
OFFSET 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/415497.html
標籤：