日期 进度2009-12-1 3.23%
2009-12-2 6.45%
2009-12-3 9.68%
2009-12-4 12.90%
2009-12-5 16.13%
2009-12-6 19.35%
2009-12-7 22.58%
2009-12-8 25.81%
2009-12-9 29.03%
2009-12-10 32.26%
2009-12-11 35.48%
2009-12-12 38.71%
2009-12-13 41.94%
2009-12-14 45.16%
2009-12-15 48.39%
2009-12-16 51.61%
2009-12-17 54.84%
2009-12-18 58.06%
2009-12-19 61.29%
2009-12-20 64.52%
2009-12-21 67.74%
2009-12-22 70.97%
2009-12-23 74.19%
2009-12-24 77.42%
2009-12-25 80.65%
2009-12-26 83.87%
2009-12-27 87.10%
2009-12-28 90.32%
2009-12-29 93.55%
2009-12-30 96.77%
2009-12-31 100.00%
2009-12-2 6.45%
2009-12-3 9.68%
2009-12-4 12.90%
2009-12-5 16.13%
2009-12-6 19.35%
2009-12-7 22.58%
2009-12-8 25.81%
2009-12-9 29.03%
2009-12-10 32.26%
2009-12-11 35.48%
2009-12-12 38.71%
2009-12-13 41.94%
2009-12-14 45.16%
2009-12-15 48.39%
2009-12-16 51.61%
2009-12-17 54.84%
2009-12-18 58.06%
2009-12-19 61.29%
2009-12-20 64.52%
2009-12-21 67.74%
2009-12-22 70.97%
2009-12-23 74.19%
2009-12-24 77.42%
2009-12-25 80.65%
2009-12-26 83.87%
2009-12-27 87.10%
2009-12-28 90.32%
2009-12-29 93.55%
2009-12-30 96.77%
2009-12-31 100.00%
CONVERT(VARCHAR(10),GETDATE(),120),
LTRIM(DAY(GETDATE())*100.0/DATEDIFF(DD,GETDATE(),DATEADD(MM,1,GETDATE()))
)+'%'
---------- -----------------------------------------
2009-12-15 48.387096774193%(所影响的行数为 1 行)
CONVERT(VARCHAR(10),GETDATE(),120),
LTRIM(CONVERT(DEC(18,2),DAY(GETDATE())*100.0/DATEDIFF(DD,GETDATE(),DATEADD(MM,1,GETDATE()))))+'%'
---------- -----------------------------------------
2009-12-15 48.39%(所影响的行数为 1 行)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([日期] varchar(10))
insert [tb]
select '2009-12-01' union all
select '2009-12-02' union all
select '2009-12-03' union all
select '2009-12-04' union all
select '2009-12-05' union all
select '2009-12-06' union all
select '2009-12-07' union all
select '2009-12-08' union all
select '2009-12-09' union all
select '2009-12-10' union all
select '2009-12-11' union all
select '2009-12-12' union all
select '2009-12-13' union all
select '2009-12-14' union all
select '2009-12-15' union all
select '2009-12-16' union all
select '2009-12-17' union all
select '2009-12-18' union all
select '2009-12-19' union all
select '2009-12-20' union all
select '2009-12-21' union all
select '2009-12-22' union all
select '2009-12-23' union all
select '2009-12-24' union all
select '2009-12-25' union all
select '2009-12-26' union all
select '2009-12-27' union all
select '2009-12-28' union all
select '2009-12-29' union all
select '2009-12-30' union all
select '2009-12-31'select *,ltrim(cast((select count(1) from [tb] where 日期<=t.日期)*100.0/
(select count(1) from [tb]) as dec(5,2))) + '%' as [进度]
from [tb] t
----------------------------------------
2009-12-01 3.23%
2009-12-02 6.45%
2009-12-03 9.68%
2009-12-04 12.90%
2009-12-05 16.13%
2009-12-06 19.35%
2009-12-07 22.58%
2009-12-08 25.81%
2009-12-09 29.03%
2009-12-10 32.26%
2009-12-11 35.48%
2009-12-12 38.71%
2009-12-13 41.94%
2009-12-14 45.16%
2009-12-15 48.39%
2009-12-16 51.61%
2009-12-17 54.84%
2009-12-18 58.06%
2009-12-19 61.29%
2009-12-20 64.52%
2009-12-21 67.74%
2009-12-22 70.97%
2009-12-23 74.19%
2009-12-24 77.42%
2009-12-25 80.65%
2009-12-26 83.87%
2009-12-27 87.10%
2009-12-28 90.32%
2009-12-29 93.55%
2009-12-30 96.77%
2009-12-31 100.00%
select day(日期)/datediff(dd,日期,dateadd(mm,1,日期)),进度 from 表
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-15 10:34:15
-- 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]([日期] datetime)
insert [tb]
select '2009-12-1' union all
select '2009-12-2' union all
select '2009-12-3' union all
select '2009-12-4' union all
select '2009-12-5' union all
select '2009-12-6' union all
select '2009-12-7' union all
select '2009-12-8' union all
select '2009-12-9' union all
select '2009-12-10' union all
select '2009-12-11' union all
select '2009-12-12' union all
select '2009-12-13' union all
select '2009-12-14' union all
select '2009-12-15' union all
select '2009-12-16' union all
select '2009-12-17' union all
select '2009-12-18' union all
select '2009-12-19' union all
select '2009-12-20' union all
select '2009-12-21' union all
select '2009-12-22' union all
select '2009-12-23' union all
select '2009-12-24' union all
select '2009-12-25' union all
select '2009-12-26' union all
select '2009-12-27' union all
select '2009-12-28' union all
select '2009-12-29' union all
select '2009-12-30' union all
select '2009-12-31'
--------------开始查询--------------------------
select
日期,
ltrim(id*100*1.0/(select max(id) from (select id=row_number()over(order by getdate()),* from tb)t))+'%' as 进度
from
(select id=row_number()over(order by getdate()),* from tb)t
group by
日期,id
----------------结果----------------------------
/* 日期 进度
----------------------- ------------------------------------------
2009-12-01 00:00:00.000 3.22580645161290322%
2009-12-02 00:00:00.000 6.45161290322580645%
2009-12-03 00:00:00.000 9.67741935483870967%
2009-12-04 00:00:00.000 12.90322580645161290%
2009-12-05 00:00:00.000 16.12903225806451612%
2009-12-06 00:00:00.000 19.35483870967741935%
2009-12-07 00:00:00.000 22.58064516129032258%
2009-12-08 00:00:00.000 25.80645161290322580%
2009-12-09 00:00:00.000 29.03225806451612903%
2009-12-10 00:00:00.000 32.25806451612903225%
2009-12-11 00:00:00.000 35.48387096774193548%
2009-12-12 00:00:00.000 38.70967741935483870%
2009-12-13 00:00:00.000 41.93548387096774193%
2009-12-14 00:00:00.000 45.16129032258064516%
2009-12-15 00:00:00.000 48.38709677419354838%
2009-12-16 00:00:00.000 51.61290322580645161%
2009-12-17 00:00:00.000 54.83870967741935483%
2009-12-18 00:00:00.000 58.06451612903225806%
2009-12-19 00:00:00.000 61.29032258064516129%
2009-12-20 00:00:00.000 64.51612903225806451%
2009-12-21 00:00:00.000 67.74193548387096774%
2009-12-22 00:00:00.000 70.96774193548387096%
2009-12-23 00:00:00.000 74.19354838709677419%
2009-12-24 00:00:00.000 77.41935483870967741%
2009-12-25 00:00:00.000 80.64516129032258064%
2009-12-26 00:00:00.000 83.87096774193548387%
2009-12-27 00:00:00.000 87.09677419354838709%
2009-12-28 00:00:00.000 90.32258064516129032%
2009-12-29 00:00:00.000 93.54838709677419354%
2009-12-30 00:00:00.000 96.77419354838709677%
2009-12-31 00:00:00.000 100.00000000000000000%(31 行受影响)
*/