27,579
社区成员
发帖
与我相关
我的任务
分享
select fno='第'+ltrim((select count(1) from @tb where e<=t.e))+'期',*
from @tb t
fno a b c d e f g
---------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- ----------------------- --------------------------------------- -----------
第1期 100000.00 20000.00 80000.00 5 2009-11-18 00:00:00.000 16000.00 11
第2期 100000.00 20000.00 80000.00 5 2009-12-18 00:00:00.000 16000.00 11
第3期 100000.00 20000.00 80000.00 5 2010-01-18 00:00:00.000 16000.00 11
第4期 100000.00 20000.00 80000.00 5 2010-02-18 00:00:00.000 16000.00 11
第5期 100000.00 20000.00 80000.00 5 2010-03-18 00:00:00.000 16000.00 11
(5 行受影响)
--> 测试数据: @tb
declare @tb table (a numeric(8,2),b numeric(7,2),c numeric(7,2),d int,e datetime,f numeric(7,2),g int)
insert into @tb
select 100000.00,20000.00,80000.00,5,'2009-11-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2009-12-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2010-01-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2010-02-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2010-03-18 00:00:00.000',16000.00,11
select fno='第'+ltrim((select count(1) from @tb where e<=t.e))+'期',*
from @tb t
fno a b c d e f g
---------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- ----------------------- --------------------------------------- -----------
第1期 100000.00 20000.00 80000.00 5 2009-11-18 00:00:00.000 16000.00 11
第2期 100000.00 20000.00 80000.00 5 2009-12-18 00:00:00.000 16000.00 11
第3期 100000.00 20000.00 80000.00 5 2010-01-18 00:00:00.000 16000.00 11
第4期 100000.00 20000.00 80000.00 5 2010-02-18 00:00:00.000 16000.00 11
第5期 100000.00 20000.00 80000.00 5 2010-03-18 00:00:00.000 16000.00 11
(5 行受影响)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-18 11:25:54
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] numeric(8,2),[b] numeric(7,2),[c] numeric(7,2),[d] int,[e] datetime,[f] numeric(7,2),[g] int)
insert [tb]
select 100000.00,20000.00,80000.00,5,'2009-11-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2009-12-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2010-01-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2010-02-18 00:00:00.000',16000.00,11 union all
select 100000.00,20000.00,80000.00,5,'2010-03-18 00:00:00.000',16000.00,11
--------------开始查询--------------------------
select FNO='第'+cast(row_number()over(order by getdate()) as varchar)+'期',* from [tb]
----------------结果----------------------------
/*
(5 行受影响)
FNO a b c d e f g
---------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- ----------------------- --------------------------------------- -----------
第1期 100000.00 20000.00 80000.00 5 2009-11-18 00:00:00.000 16000.00 11
第2期 100000.00 20000.00 80000.00 5 2009-12-18 00:00:00.000 16000.00 11
第3期 100000.00 20000.00 80000.00 5 2010-01-18 00:00:00.000 16000.00 11
第4期 100000.00 20000.00 80000.00 5 2010-02-18 00:00:00.000 16000.00 11
第5期 100000.00 20000.00 80000.00 5 2010-03-18 00:00:00.000 16000.00 11
(5 行受影响)
*/