27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-05-02 03:47:37
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([欠费年份] int,[欠费开始月份] int,[欠费结束月份] int,[应缴金额] numeric(3,1))
insert [tb]
select 2001,9,12,94.4 union all
select 2008,5,12,88.8 union all
select 2010,8,12,90.4
--------------开始查询--------------------------
select
tid=row_number()over(order by getdate()),ltrim(欠费年份)+'-'+ltrim(right(100+number,2)) as date,b.应缴金额 as money
from
master..spt_values a, tb b
where
number between 欠费开始月份 and 欠费结束月份
and
type='p'
----------------结果----------------------------
/*
(3 行受影响)
tid date money
-------------------- ----------------- ---------------------------------------
1 2001-09 94.4
2 2001-10 94.4
3 2001-11 94.4
4 2001-12 94.4
5 2008-05 88.8
6 2008-06 88.8
7 2008-07 88.8
8 2008-08 88.8
9 2008-09 88.8
10 2008-10 88.8
11 2008-11 88.8
12 2008-12 88.8
13 2010-08 90.4
14 2010-09 90.4
15 2010-10 90.4
16 2010-11 90.4
17 2010-12 90.4
(17 行受影响)
*/