By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE myTable
SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC;
SHOW CREATE TABLE myTable;
SELECT * FROM myTable;
| Table | Create Table |
|---|---|
| myTable | CREATE TABLE `myTable` ( `CI_NUM` varchar(10) NOT NULL DEFAULT '', `DL_DT` varchar(8) NOT NULL DEFAULT '', `DL_PRC` bigint NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| CI_NUM | DL_DT | DL_PRC |
|---|---|---|
| 1000000001 | 20210701 | 700 |
| 1000000001 | 20210701 | 500 |
| 1000000001 | 20210701 | 600 |
| 1000000001 | 20210805 | 600 |
| 1000000001 | 20210805 | 350 |
| 1000000001 | 20210805 | 400 |
| 2000000001 | 20210510 | 300 |
| 2000000001 | 20210510 | 350 |
| 2000000001 | 20210510 | 200 |
| 2000000001 | 20210603 | 700 |
| 2000000001 | 20210603 | 650 |
| 2000000001 | 20210603 | 400 |
SELECT CI_NUM, DL_DT,
ROW_NUMBER() OVER (PARTITION BY CI_NUM, DL_DT) AS SQ,
DL_PRC
FROM myTable;
SELECT CI_NUM, DL_DT,
CASE WHEN @cn = CI_NUM AND @dd = DL_DT THEN @rownum := @rownum+1
ELSE @rownum := 1 END AS SQ,
DL_PRC,
@cn := CI_NUM,
@dd := DL_DT
FROM (SELECT @cn := NULL, @dd:= NULL, @rownum := 1) v
CROSS JOIN myTable
;
| CI_NUM | DL_DT | SQ | DL_PRC |
|---|---|---|---|
| 1000000001 | 20210701 | 1 | 700 |
| 1000000001 | 20210701 | 2 | 500 |
| 1000000001 | 20210701 | 3 | 600 |
| 1000000001 | 20210805 | 1 | 600 |
| 1000000001 | 20210805 | 2 | 350 |
| 1000000001 | 20210805 | 3 | 400 |
| 2000000001 | 20210510 | 1 | 300 |
| 2000000001 | 20210510 | 2 | 350 |
| 2000000001 | 20210510 | 3 | 200 |
| 2000000001 | 20210603 | 1 | 700 |
| 2000000001 | 20210603 | 2 | 650 |
| 2000000001 | 20210603 | 3 | 400 |
| CI_NUM | DL_DT | SQ | DL_PRC | @cn := CI_NUM | @dd := DL_DT |
|---|---|---|---|---|---|
| 1000000001 | 20210701 | 1 | 700 | 1000000001 | 20210701 |
| 1000000001 | 20210701 | 2 | 500 | 1000000001 | 20210701 |
| 1000000001 | 20210701 | 3 | 600 | 1000000001 | 20210701 |
| 1000000001 | 20210805 | 1 | 600 | 1000000001 | 20210805 |
| 1000000001 | 20210805 | 2 | 350 | 1000000001 | 20210805 |
| 1000000001 | 20210805 | 3 | 400 | 1000000001 | 20210805 |
| 2000000001 | 20210510 | 1 | 300 | 2000000001 | 20210510 |
| 2000000001 | 20210510 | 2 | 350 | 2000000001 | 20210510 |
| 2000000001 | 20210510 | 3 | 200 | 2000000001 | 20210510 |
| 2000000001 | 20210603 | 1 | 700 | 2000000001 | 20210603 |
| 2000000001 | 20210603 | 2 | 650 | 2000000001 | 20210603 |
| 2000000001 | 20210603 | 3 | 400 | 2000000001 | 20210603 |
SELECT CI_NUM, DL_DT,
ROW_NUMBER() OVER (PARTITION BY CI_NUM, DL_DT) AS SQ,
DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
UNION ALL
SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
UNION ALL
SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;
| CI_NUM | DL_DT | SQ | DL_PRC |
|---|---|---|---|
| 1000000001 | 20210701 | 1 | 700 |
| 1000000001 | 20210701 | 2 | 500 |
| 1000000001 | 20210701 | 3 | 600 |
| 1000000001 | 20210805 | 1 | 600 |
| 1000000001 | 20210805 | 2 | 350 |
| 1000000001 | 20210805 | 3 | 400 |
| 2000000001 | 20210510 | 1 | 300 |
| 2000000001 | 20210510 | 2 | 350 |
| 2000000001 | 20210510 | 3 | 200 |
| 2000000001 | 20210603 | 1 | 700 |
| 2000000001 | 20210603 | 2 | 650 |
| 2000000001 | 20210603 | 3 | 400 |