http://topic.csdn.net/u/20090314/00/b2fd1593-96a7-4732-af2b-e67a1655499e.html?711201441参考这个帖,需要使用油标或循环来做.
解决方案 »
- 日期时间2008-10-02 10:06:25.000后面的000是什么意思?
- 刚才没有一个对的!可能我说清楚!我再说一边
- 修改数据表中的字段长度,SQL语句怎么写?
- sql server7.0英文版 数据库的属性:Code Page:cp 1252 而另一台sql server7.0英文版Code Page:cp 936 why
- sqlserver的个人版是不是不支持网络连接
- fulltext的索引短字符串问题
- 求助 请问怎么算这种平均数
- 走过路过的朋友,请来看看吧!!
- SQL SERVER 2000里的SQL Mail不能发送html格式的邮件吗?
- 自定义函数 报错问题
- 数据批量修改
- 动态导航条问题
go
create table tb (KHH varchar(10),KH varchar(10),FKQ datetime,BZ varchar(10),SJSKRQ datetime)
insert tb select 'A1' , '001' , '2008-01-01', '正常还款', '2008-01-09'
insert tb select 'A1' , '001' , '2008-02-01', '正常还款', '2008-02-18'
insert tb select 'A1' , '001' , '2008-03-01', '欠款', '2008-04-09'
insert tb select 'A1' , '001' , '2008-04-01', '正常还款', '2008-04-09'
insert tb select 'B1' , '002' , '2008-12-01', '正常还款', '2008-12-30'
insert tb select 'B1' , '002' , '2009-01-01', '正常还款', '2009-01-02'
insert tb select 'B1' , '002' , '2009-02-01', '正常还款', '2009-02-28'
insert tb select 'B1' , '002' , '2009-03-01', '正常还款', '2009-03-11'
goalter table tb add Fid int
godeclare @Khh varchar(10),@Fid int
update tb set fid=@fid,@fid=case when KHH=isnull(@Khh,KHH) and BZ='正常还款' then isnull(@fid,0)+1 when KHH=isnull(@Khh,KHH) and BZ='欠款' then 0 else 1 end,@Khh=KHHselect * from tb where KHH in(select KHH from tb where fid=4)KHH KH FKQ BZ SJSKRQ Fid
---------- ---------- ------------------------------------------------------ ---------- ------------------------------------------------------ -----------
B1 002 2008-12-01 00:00:00.000 正常还款 2008-12-30 00:00:00.000 1
B1 002 2009-01-01 00:00:00.000 正常还款 2009-01-02 00:00:00.000 2
B1 002 2009-02-01 00:00:00.000 正常还款 2009-02-28 00:00:00.000 3
B1 002 2009-03-01 00:00:00.000 正常还款 2009-03-11 00:00:00.000 4(所影响的行数为 4 行)
if object_id('tb') is not null drop table tb
go
create table tb (KHH varchar(10),KH varchar(10),FKQ datetime,BZ varchar(10),SJSKRQ datetime)
insert tb select 'A1' , '001' , '2008-01-01', '正常还款', '2008-01-09'
insert tb select 'A1' , '001' , '2008-02-01', '正常还款', '2008-02-18'
insert tb select 'A1' , '001' , '2008-03-01', '欠款', '2008-04-09'
insert tb select 'A1' , '001' , '2008-04-01', '正常还款', '2008-04-09'
insert tb select 'B1' , '002' , '2008-12-01', '正常还款', '2008-12-30'
insert tb select 'B1' , '002' , '2009-01-01', '正常还款', '2009-01-02'
insert tb select 'B1' , '002' , '2009-02-01', '正常还款', '2009-02-28'
insert tb select 'B1' , '002' , '2009-03-01', '正常还款', '2009-03-11'
goalter table tb add Fid int
godeclare @Khh varchar(10),@Fid int
update tb set fid=@fid,@fid=case when KHH=isnull(@Khh,KHH) and BZ='正常还款' then isnull(@fid,0)+1 when KHH=isnull(@Khh,KHH) and BZ='欠款' then 0 else 1 end,@Khh=KHHselect * from tb where KHH in(select KHH from tb where fid>=3)
select KHH,KH
from T
where year(FKQ)=year(SJKRQ) and month(FKQ)=year(SJSKRQ)
group by KHH,KH
having (count(*)>=4)没测试
--建立测试表
if object_id('tb') is not null drop table tb
go
create table tb (KHH varchar(10),KH varchar(10),FKQ datetime,BZ varchar(10),SJSKRQ datetime)
insert tb select 'A1' , '001' , '2008-01-01', '正常还款', '2008-01-09'
insert tb select 'A1' , '001' , '2008-02-01', '正常还款', '2008-02-18'
insert tb select 'A1' , '001' , '2008-03-01', '欠款', '2008-04-09'
insert tb select 'A1' , '001' , '2008-04-01', '正常还款', '2008-04-09'
insert tb select 'B1' , '002' , '2008-12-01', '正常还款', '2008-12-30'
insert tb select 'B1' , '002' , '2009-01-01', '正常还款', '2009-01-02'
insert tb select 'B1' , '002' , '2009-02-01', '正常还款', '2009-02-28'
insert tb select 'B1' , '002' , '2009-03-01', '正常还款', '2009-03-11'
go--处理过程
alter table tb add Fid int --增加辅助计算列
go
declare @Khh varchar(10),@Fid int
update tb set fid=@fid,@fid=case when KHH=isnull(@Khh,KHH) and BZ='正常还款' then isnull(@fid,0)+1 when KHH=isnull(@Khh,KHH) and BZ='欠款' then 0 else 1 end,@Khh=KHH --更新辅助计算列
--提取结果
select distinct KHH,KH from tb where fid>=3
alter table tb drop column Fid --删除辅助计算列
drop table tb --删除测试表
--结果/*
KHH KH
---------- ----------
B1 002(所影响的行数为 1 行)
*/
这个是不可以的,有的客户是不连续的
if object_id('tb') is not null drop table tb
go
create table tb (KHH varchar(10),KH varchar(10),FKQ datetime,BZ varchar(10),SJSKRQ datetime)
insert tb select 'A1' , '001' , '2008-01-01', '正常还款', '2008-01-09'
insert tb select 'A1' , '001' , '2008-02-01', '正常还款', '2008-02-18'
insert tb select 'A1' , '001' , '2008-03-01', '欠款', '2008-04-09'
insert tb select 'A1' , '001' , '2008-04-01', '正常还款', '2008-04-09'
insert tb select 'B1' , '002' , '2008-12-01', '正常还款', '2008-12-30'
insert tb select 'B1' , '002' , '2009-01-01', '正常还款', '2009-01-02'
insert tb select 'C1' , '003' , '2008-02-01', '正常还款', '2008-02-18'
insert tb select 'C1' , '003' , '2008-03-01', '欠款', '2008-04-09'
insert tb select 'B1' , '002' , '2009-02-01', '正常还款', '2009-02-28'
insert tb select 'B1' , '002' , '2009-03-01', '正常还款', '2009-03-11'go--处理过程
alter table tb add Fid int --增加辅助计算列
go
--增加临时表
select * into # from tb order by KHH,KH
declare @Khh varchar(10),@Fid int
update # set fid=@fid,@fid=case when KHH=isnull(@Khh,KHH) and BZ='正常还款' then isnull(@fid,0)+1 when KHH=isnull(@Khh,KHH) and BZ='欠款' then 0 else 1 end,@Khh=KHH --更新辅助计算列
--提取结果
select distinct KHH,KH from # where fid>=3
alter table tb drop column Fid --删除辅助计算列
drop table tb,# --删除测试表及临时表
--结果/*
KHH KH
---------- ----------
B1 002(所影响的行数为 1 行)
*/