如何从选择查询中统计重复记录

本文关键字：统计 记录 查询 选择 | 更新日期: 2023-09-27 18:26:32

给定此表：

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

我想添加一个包含序列号的列，这样图案、样式和颜色的每个组合都有一个迭代的序列号。例如：

输出

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**

如何编写查询以生成此序列号？

如何从选择查询中统计重复记录

您可以使用窗口函数ROW_NUMBER:

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

LiveDemo

输出：

╔═══════╦═════════╦═══════╦═════════╦══════╦═══════╗
║ 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 ║
╚═══════╩═════════╩═══════╩═════════╩══════╩═══════╝
相关文章：