如何从select查询中计数重复记录

Given this table:

鉴于这种表:

CUTNO    **pattern  style    color**    size    qty  
c0001    jr-7551    SHI      denim-x    10      100  
c0002    jr-7571    HTD      stone      12      250  
c0003    jr-7586    HTD      denim-x    18      200  
c0004    **jr-7512  B/E      mud**      14      500  
c0005    jr-7512    B/E      stone      16      5  
c0006    **jr-7512  B/E      mud**      8       15

I want to add a column that contains a sequence number such that each combination of pattern, style and color has an iterated sequence number. For example:

我想添加一个列，该列包含一个序列号，使模式、样式和颜色的每个组合都具有迭代的序列号。例如:

output

输出

CUTNO    pattern      style    color    size  qty     **seqno**  
c0001    jr-7551      SHI      denim    10    100     **1**  
c0002    jr-7571      HTD      stone    12    250     **1**  
c0003    jr-7586      HTD      denim    18    200     **1**  
c0004    **jr-7512    B/E      mud**    14    500     **1**  
c0005    **jr-7512    B/E      stone**  1     65      **1**  
c0006    **jr-7512    B/E      mud**    8     15      **2**  
c0007    **jr-7512    B/E      stone**  5     300     **2**  
c0008    **jr-7512    B/E      mud**    1     20      **3**

How do I write a query to produce this sequence number?

如何编写查询来生成这个序列号?

1 个解决方案

#1

You can use windowed function ROW_NUMBER:

可以使用窗口函数ROW_NUMBER:

SELECT *,
  [seqno] = ROW_NUMBER() OVER(PARTITION BY pattern, style, color ORDER BY CUTNO)
FROM your_table
ORDER BY CUTNO;

LiveDemo

LiveDemo

Output:

输出:

╔═══════╦═════════╦═══════╦═════════╦══════╦═══════╗
║ CUTNO ║ pattern ║ style ║  color  ║ size ║ seqno ║
╠═══════╬═════════╬═══════╬═════════╬══════╬═══════╣
║ c0001 ║ jr-7551 ║ SHI   ║ denim-x ║   10 ║     1 ║
║ c0002 ║ jr-7571 ║ HTD   ║ stone   ║   12 ║     1 ║
║ c0003 ║ jr-7586 ║ HTD   ║ denim-x ║   18 ║     1 ║
║ c0004 ║ jr-7512 ║ B/E   ║ mud     ║   14 ║     1 ║
║ c0005 ║ jr-7512 ║ B/E   ║ stone   ║   16 ║     1 ║
║ c0006 ║ jr-7512 ║ B/E   ║ mud     ║    8 ║     2 ║
║ c0007 ║ jr-7512 ║ B/E   ║ stone   ║    5 ║     2 ║
║ c0008 ║ jr-7512 ║ B/E   ║ mud     ║    1 ║     3 ║
╚═══════╩═════════╩═══════╩═════════╩══════╩═══════╝

#1