create table test(保单号 varchar(20), 交费金额 decimal(24, 2), 主附险记录 varchar(10))
insert into test values('OR001', 388.8, '主')
insert into test values('OR002', 207.00, '主')
insert into test values('OR001', 17.8, '附')
insert into test values('OR001', 388.8, '主')
insert into test values('OR001', 17.8, '附')
insert into test values('OR003', 219.40, '主')
insert into test values('OR003', 15.0, '附')
insert into test values('OR001', 388.8, '主')
insert into test values('OR001', 17.8, '附')
insert into test values('OR003', 219.40, '主')
insert into test values('OR003', 15.0, '附')
写个查询交费记录(保单当天交了几笔费用)
保单号 交费金额 交费次数
OR001 406.6 1
OR001 406.6 2
OR001 406.6 3
OR002 207.00 1
OR003 234.40 1
OR003 234.40 2不用单号排序也可以
insert into test values('OR001', 388.8, '主')
insert into test values('OR002', 207.00, '主')
insert into test values('OR001', 17.8, '附')
insert into test values('OR001', 388.8, '主')
insert into test values('OR001', 17.8, '附')
insert into test values('OR003', 219.40, '主')
insert into test values('OR003', 15.0, '附')
insert into test values('OR001', 388.8, '主')
insert into test values('OR001', 17.8, '附')
insert into test values('OR003', 219.40, '主')
insert into test values('OR003', 15.0, '附')
写个查询交费记录(保单当天交了几笔费用)
保单号 交费金额 交费次数
OR001 406.6 1
OR001 406.6 2
OR001 406.6 3
OR002 207.00 1
OR003 234.40 1
OR003 234.40 2不用单号排序也可以
select 保单号,sum(交费金额) as 交费金额,count(1) as 交费次数 from test group by 保单号
select 保单号,sum(交费金额) as 交费金额,count(1) as 交费次数 from test group by 保单号 order by 保单号
保单号,
sum(交费金额) as 交费金额,
row_number() over(partition by 保单号 order by getdate()) as 交费次数
from
(select *,rn=row_number() over(partition by 保单号 order by getdate()) from test)t
group by
保单号,(rn-1)/2
order by
1,3/*8
保单号 交费金额 交费次数
-------------------- --------------------------------------- --------------------
OR001 406.60 1
OR001 406.60 2
OR001 406.60 3
OR002 207.00 1
OR003 234.40 1
OR003 234.40 2(6 行受影响)
**/
insert into test values('OR001', 388.8, '主', '2010-07-01')
insert into test values('OR002', 207.00, '主', '2010-07-01')
insert into test values('OR001', 17.8, '附', '2010-07-01')
insert into test values('OR001', 388.8, '主', '2010-07-01')
insert into test values('OR001', 17.8, '附', '2010-07-01')
insert into test values('OR003', 219.40, '主', '2010-07-01')
insert into test values('OR003', 15.0, '附', '2010-07-01')
insert into test values('OR001', 388.8, '主', '2010-07-01')
insert into test values('OR001', 17.8, '附', '2010-07-01')
insert into test values('OR003', 219.40, '主', '2010-07-01')
insert into test values('OR003', 15.0, '附', '2010-07-01')insert into test values('OR001', 388.8, '主', '2010-07-02')
insert into test values('OR001', 17.8, '附', '2010-07-02')
insert into test values('OR001', 388.8, '主', '2010-07-02')
insert into test values('OR001', 17.8, '附', '2010-07-02')sql 是写的存储过程 每日运行
想得到结果是
保单号 交费金额 交费次数
OR001 406.6 1
OR001 406.6 2
OR001 406.6 3
OR002 207.00 1
OR003 234.40 1
OR003 234.40 2
OR001 406.6 4
OR001 406.6 5
if object_id('test') is not null
drop table test
gocreate table test(保单号 varchar(20), 交费金额 decimal(24, 2), 主附险记录 varchar(10), 交费日期 datetime)
insert into test values('OR001', 388.8, '主', '2010-07-01')
insert into test values('OR002', 207.00, '主', '2010-07-01')
insert into test values('OR001', 17.8, '附', '2010-07-01')
insert into test values('OR001', 388.8, '主', '2010-07-01')
insert into test values('OR001', 17.8, '附', '2010-07-01')
insert into test values('OR003', 219.40, '主', '2010-07-01')
insert into test values('OR003', 15.0, '附', '2010-07-01')
insert into test values('OR001', 388.8, '主', '2010-07-01')
insert into test values('OR001', 17.8, '附', '2010-07-01')
insert into test values('OR003', 219.40, '主', '2010-07-01')
insert into test values('OR003', 15.0, '附', '2010-07-01')
insert into test values('OR001', 388.8, '主', '2010-07-02')
insert into test values('OR001', 17.8, '附', '2010-07-02')
insert into test values('OR001', 388.8, '主', '2010-07-02')
insert into test values('OR001', 17.8, '附', '2010-07-02')select
保单号,
sum(交费金额) as 交费金额,
row_number() over(partition by 保单号,交费日期 order by getdate()) as 交费次数 ,
交费日期
from
(select *,rn=row_number() over(partition by 保单号,交费日期 order by getdate()) from test)t
group by
保单号,(rn-1)/2 ,交费日期
order by
1,4,3输出结果:
/*
OR001 406.60 1 2010-07-01 00:00:00.000
OR001 406.60 2 2010-07-01 00:00:00.000
OR001 406.60 3 2010-07-01 00:00:00.000
OR001 406.60 1 2010-07-02 00:00:00.000
OR001 406.60 2 2010-07-02 00:00:00.000
OR002 207.00 1 2010-07-01 00:00:00.000
OR003 234.40 1 2010-07-01 00:00:00.000
OR003 234.40 2 2010-07-01 00:00:00.000*/