MS SQL Server中的基本游标 [英] Basic Cursor in MS SQL Server

问题描述

我正在寻找在MSSS上使用Cursor的基本方向。

I am looking for basic direction on Cursor use in MSSS.

说有一个表， TABLE1 ，具有2个字段（ ID ， Date ）。 ID 是不是唯一键。该表按id记录事件，一些id经常发生，一些不经常发生。

Say there is a table, TABLE1, with 2 fields (ID, Date). The ID is not a unique key. The table records events by id, and some ids occur frequently, some infrequently.

例如：

ID  |  Date
1   |  2010-01-01
2   |  2010-02-01
3   |  2010-02-15
2   |  2010-02-15
4   |  2010-03-01

我想创建一个包含以下字段的表：ID，Date ，ID在次日之前6个月出现的次数，ID出现在日期后6个月内的次数。

I would like to create a new table with the following fields: ID, Date, Number of times ID appears in 6 months previous to Date, Number of times ID appears in 6 months after Date.

有最好的方法去完成这个吗？非常感谢。

Is there a best way to go about accomplishing this? Thanks kindly.

推荐答案

这是一方（我认为 - 未测试）

This is one side (I think - not tested)

select t1.id, t1.date, count(*) as 'count'
from table t1 
join table t2 
  on t2.id = t1.id
 and DateDiff(mm,t1.date,t2.date) <= 6 
 and DateDiff(mm,t1.date,t2.date) >  0 
group by t1.id, t1.date

我想你可以跳过> 0和用例计数正和负值

I think you can skip the > 0 and use case to count the positive and negative

sum(WHEN t1.date > t2.date then 0 else 1) as prior 
sum(WHEN t1.date < t2.date then 0 else 1) as next 

and DateDiff(mm,t1.date,t2.date) <= 6 
and DateDiff(mm,t2.date,t2.date) <= 6 

有

May have prior and next backwards

这篇关于MS SQL Server中的基本游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！