SQL保留出现次数 [英] SQL Keeping count of occurrences

问题描述

我需要在SQL中解决以下问题。

假设我有一个包含两列的表：

  Date |代码
 -------- 
 0 | 25 
 1 | 22 
 2 | 23 
 3 | 25 
 4 | 23 
 5 | 21 
  

我需要记住他们，因为日期与我相关。所以，让我们说，我需要生成这样的东西：

  Date |代码|计数
 -------------------- 
 0 | 25 | 1 
 1 | 22 | 1 
 2 | 23 | 1 
 3 | 25 | 2 
 4 | 23 | 2 
 5 | 21 | 1 
  

提前感谢，

：

解决方案

p>最简单的（也许最有效的）方法是使用 ROW_NUMBER（）：

 code> SELECT [Date]，Code，[Count] = ROW_NUMBER（）OVER（PARTITION BY Code ORDER BY [Date]）
 FROM dbo.YourTableName 
 ORDER BY [Date]; 
  

有趣的是，你也可以在SQL Server 2012中解决这个问题。如果 Date 是唯一的：

  ）OVER（PARTITION BY代码ORDER BY [Date] 
 RANGE UNBOUNDED PRECEDING）
 FROM dbo.YourTable 
 ORDER BY [Date]; 
  

或更简单：

  SELECT [Date]，Code，[Count] = COUNT（*）OVER（PARTITION BY Code ORDER BY [Date]）
 FROM dbo.YourTable 
 ORDER BY [Date] ; 
  

如果 Date 不是唯一的，你不需要绑定（对于相同的日期+代码组合），你需要使用在磁盘spool上使用的更贵的 ROWS ：

  SELECT [Date]，Code，[Count] = COUNT（*）OVER（PARTITION BY Code ORDER BY [Date] 
 ROWS UNBOUNDED PRECEDING）
 FROM dbo.YourTable 
 ORDER BY [Date]; 
  

您可能想在表格上尝试这些选项，看看效果如何。 / p>

I've the following problem I need to solve in SQL.

Let's say that I have a table with 2 columns:

    Date | Code
    --------
    0    | 25
    1    | 22
    2    | 23
    3    | 25
    4    | 23
    5    | 21

And I need to keep a count of them, as the Date is relevant for me. So, let's say that I would need to produce something like this:

    Date | Code | Count
    --------------------
    0    | 25   |   1
    1    | 22   |   1
    2    | 23   |   1
    3    | 25   |   2
    4    | 23   |   2
    5    | 21   |   1

Thanks in advance,

PS: I'm implementing it in MSSQL 2012.

Regards.

解决方案

The simplest (and probably most efficient) approach is to use ROW_NUMBER():

SELECT [Date], Code, [Count] = ROW_NUMBER() OVER (PARTITION BY Code ORDER BY [Date])
 FROM dbo.YourTableName
 ORDER BY [Date];

For fun, you can also solve it this way in SQL Server 2012. If Date is unique:

SELECT [Date], Code, [Count] = COUNT(*) OVER (PARTITION BY Code ORDER BY [Date]
  RANGE UNBOUNDED PRECEDING)
 FROM dbo.YourTable
 ORDER BY [Date];

Or more simply:

SELECT [Date], Code, [Count] = COUNT(*) OVER (PARTITION BY Code ORDER BY [Date])
 FROM dbo.YourTable
 ORDER BY [Date];

If Date is not unique, and if you don't want ties (same count for identical combinations of date+code), you need to use the more expensive ROWS, which uses on on-disk spool:

SELECT [Date], Code, [Count] = COUNT(*) OVER (PARTITION BY Code ORDER BY [Date]
  ROWS UNBOUNDED PRECEDING)
 FROM dbo.YourTable
 ORDER BY [Date];

You may want to try each of these options on your table to see what the performance is like.

这篇关于SQL保留出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！