您在寻找这样的东西吗?
-- Next No calculated for current month
SELECT CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
'-',
LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_no), 4) AS INT), 0) + 1 AS VARCHAR(4)), 4, '0')) next_no
FROM orders
WHERE CAST(LEFT(order_no, STRPOS(order_no, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE)
;
Output:
| NEXT_NO |
-----------
| 5-0001 |
假设有一条记录order_no
= '1-0001' 那么
-- Next No for January
SELECT CONCAT(CAST(DATE_PART('MONTH', DATE '2013-01-01') AS VARCHAR(2)),
'-',
LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_no), 4) AS INT), 0) + 1 AS VARCHAR(4)), 4, '0')) next_no
FROM orders
WHERE CAST(LEFT(order_no, STRPOS(order_no, '-') - 1) AS INT) = DATE_PART('MONTH', DATE '2013-01-01')
;
Output:
| NEXT_NO |
-----------
| 1-0002 |
SQLFiddle