对列sql中的行重复值进行计数

Question

我需要一个查询来获得以下结果。其目的是计算一列在articlecode中的重复值。此外，应该截断或显示重复的行。

Table_1：(在使用query之前)

processname         articlecode     articlename    stock    createdon
STITCHING PRODUCT   TH11592-9192    M/C EMB TOP PCS 2000    8/16/2021
STITCHING PRODUCT   TH11592-9192    M/C EMB TOP PCS 6200    8/23/2021
STITCHING PRODUCT   TH11592-9192    M/C EMB TOP PCS 4450    8/24/2021
STITCHING PRODUCT   TH11592-9193    BACK CUT PCS    4000    8/14/2021
STITCHING PRODUCT   TH11592-9193    BACK CUT PCS    6200    8/23/2021
STITCHING PRODUCT   TH11592-9259    LINNING CUT PCS 4000    8/14/2021
STITCHING PRODUCT   TH11592-9259    LINNING CUT PCS 2000    8/16/2021
STITCHING PRODUCT   TH11592-9259    LINNING CUT PCS 6200    8/23/2021

Table_1：(使用query之后，表应该如下所示)

processname         articlecode     articlename     stock   createdon   rown
STITCHING PRODUCT   TH11592-9192    M/C EMB TOP PCS 2000    8/16/2021   1
STITCHING PRODUCT   TH11592-9192    M/C EMB TOP PCS 6200    8/23/2021   1
STITCHING PRODUCT   TH11592-9192    M/C EMB TOP PCS 4450    8/24/2021   1
STITCHING PRODUCT   TH11592-9193    BACK CUT PCS    4000    8/14/2021   2
STITCHING PRODUCT   TH11592-9193    BACK CUT PCS    6200    8/23/2021   2
STITCHING PRODUCT   TH11592-9259    LINNING CUT PCS 4000    8/14/2021   3
STITCHING PRODUCT   TH11592-9259    LINNING CUT PCS 2000    8/16/2021   3
STITCHING PRODUCT   TH11592-9259    LINNING CUT PCS 6200    8/23/2021   3

我需要基于articlecode的行列计数

Answer 1

使用DENSE_RANK，我们可以尝试：

SELECT *, DENSE_RANK() OVER (ORDER BY articlecode) AS rown
FROM Table_1
ORDER BY articlecode, createdon;