计算连续数字 [英] Count consecutive numbers
问题描述
我正在尝试提取连续数字的计数,或者未破坏的数字。在这种情况下,在任何特定的时间都可以使用
。
例如(简化):
CREATE表#Customers
(
CustNo INT,
YearNo INT,
IsCust CHAR(1)
)
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2006,''Y'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2005,''Y'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2004,''Y '')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2003,'N'')
INSERT INTO #Customers(custno,yearno ,isCust)VALUES(999,2002,''N'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2001,''Y'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2000,''Y'')
SELECT * FROM #Customers
CustNo YearNo IsCust
----------- ----------- ------
999 2006 Y
999 2005 Y
999 2004 Y
999 2003 N
999 2002 N
999 2001 Y
999 2000 Y
2006年,CustNo 999将活跃3年,2004年为2001年1月,为
2等理想情况下我会用一年的时间来查询
我在这里拒绝创建光标的冲动 - 任何人都有任何提示吗?
>
.... Chris。
I''m trying extract a count of consecutive numbers, or "unbroken" years in
this case, at any particular given time.
For example (simplified):
CREATE TABLE #Customers
(
CustNo INT,
YearNo INT,
IsCust CHAR(1)
)
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, ''N'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, ''N'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, ''Y'')
SELECT * FROM #Customers
CustNo YearNo IsCust
----------- ----------- ------
999 2006 Y
999 2005 Y
999 2004 Y
999 2003 N
999 2002 N
999 2001 Y
999 2000 Y
In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
2, etc. Ideally I''d feed it a single year to lookup
I''m resisting the urge to create cursor here -- anyone have any hints?
....Chris.
推荐答案
ChrisD写道:
ChrisD wrote:
2006年,CustNo 999将活跃3年,2004年1月,2001年
2,等等。理想情况下我会用一年的时间来查找
In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001
for 2, etc. Ideally I''d feed it a single year to lookup
>
这适用于Postgres,你必须改变限制1。到mssql TOP 1
语法。另请注意第4行的硬编码年份,用
参数替换它。
具有讽刺意味的是,只有在指定年份时才有效。如果没有这一年你就会得到虚假的行。
选择a.yearno,b.yearno,(a.yearno - b.yearno)+ 1作为 ;年度
来自客户的加盟客户b on a.custno = b.custno
其中a.yearno> b.yearno
和a.yearno = 2006
和a.isCust =''Y''和b.isCust =''Y''
且不存在
(
选择年份
来自客户x
WHERE x.custno = a .custno
和x.yearno在b.yearno和a.yearno之间
AND x.isCust =''N''
)
由b.yearno订购
限制1
-
Kenneth Downs
安全数据软件公司
(Ken)nneth @(Sec)ure(Dat)a(.com)
This works in Postgres, you''ll have to change the "limit 1" to mssql TOP 1
syntax. Also note the hardcoded year on line 4, replace that with a
parameter.
Ironically, this only works if you specify the year. Without the year you
get spurious rows.
select a.yearno,b.yearno,(a.yearno - b.yearno) + 1 as "years"
from customers a join customers b on a.custno = b.custno
where a.yearno > b.yearno
AND a.yearno = 2006
AND a.isCust = ''Y'' and b.isCust = ''Y''
and not exists
(
select yearno
FROM customers x
WHERE x.custno = a.custno
AND x.yearno between b.yearno AND a.yearno
AND x.isCust = ''N''
)
order by b.yearno
limit 1
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
" ChrisD" < SP ******** @ hotmail.com>在消息新闻中写道:< Yfn2e.840846
"ChrisD" <sp********@hotmail.com> wrote in message news:<Yfn2e.840846
Xk.593396@pd7tw3no> ...
Xk.593396@pd7tw3no>...
我正在尝试提取连续数字的计数,或不间断的或在这种情况下,在任何特定的时间。
例如(简化):
CREATE TABLE #Customers
(
CustNo INT,
YearNo INT,
IsCust CHAR(1)
插入#Customers(custno,yearno,isCust)VALUES(999,2006,' 'Y'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2005,''Y'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999) ,2004,''Y'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2003,'N'')
INSERT INTO #Customers(custno,yearno,isCust )VALUES(999,2002,'N'')
INSERT INTO #Customers(custno,yearno,isCust)VALUES(999,2001,''Y'')
INSERT INTO #Customers(custno) ,yearno,isCust)VALUES(999,2000,''Y'')
SELECT * FROM #Customers
8< ------明显的结果被忽略
2006年,CustNo 999将活跃3年,2004年为2001年1月,
2,理想情况下,我会在一年内用它来查询
我在这里拒绝创建光标的冲动 - 任何人都有任何提示吗?
.. .Chris。
I''m trying extract a count of consecutive numbers, or "unbroken" years in
this case, at any particular given time.
For example (simplified):
CREATE TABLE #Customers
(
CustNo INT,
YearNo INT,
IsCust CHAR(1)
)
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, ''N'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, ''N'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, ''Y'')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, ''Y'')
SELECT * FROM #Customers
8<------ Obvious result omitted
In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
2, etc. Ideally I''d feed it a single year to lookup
I''m resisting the urge to create cursor here -- anyone have any hints?
...Chris.
你想要执行的计算是一个减法。
你的数据有一些警告。
只需两个提示......
The computation you want to perform is a subtraction.
There are some caveats concernig your data.
Just two hints ...
这篇关于计算连续数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!