-->生成测试数据: GO IF OBJECT_ID('TBL')IS NOT NULL DROP TABLE TBL GO CREATE TABLE TBL( 日期 DATE ) GO INSERT TBL SELECT '2002-03-01' UNION ALL SELECT '2012-03-01' --利用递归实现输出三月份的所有日期: go declare @date date select @date=MAX(日期) from tbl ;with t as( select * from tbl union all select dateadd(YY,1,a.日期) from t a where not exists(select * from tbl b where b.日期=DATEADD(YY,1,a.日期) ) and a.日期<@date ) select convert(varchar(4),日期,120)+'年' as 年份 from t order by 日期/* 年份 2002年 2003年 2004年 2005年 2006年 2007年 2008年 2009年 2010年 2011年 2012年 */
方法一:创建存储过程实现 --如何用"最小缺失数"实现确实日期的自动补全 -->生成测试数据: GO IF OBJECT_ID('TBL')IS NOT NULL DROP TABLE TBL GO CREATE TABLE TBL( 日期 DATE, 备注 VARCHAR(100) ) GO INSERT TBL SELECT '2012-03-02','B' UNION ALL SELECT '2012-03-05','C' UNION ALL SELECT '2012-03-06','D' UNION ALL SELECT '2012-03-07','E' UNION ALL SELECT '2012-03-09','F' UNION ALL SELECT '2012-03-11','G' UNION ALL SELECT '2012-03-12','H' UNION ALL SELECT '2012-03-13','I' UNION ALL SELECT '2012-03-15','J' UNION ALL SELECT '2012-03-19','K' UNION ALL SELECT '2012-03-20','L' GO IF OBJECT_ID('P_SP')IS NOT NULL DROP PROC P_SP GO CREATE PROC P_SP @ENDTIME DATE AS DECLARE @SQL VARCHAR(100) SET @SQL='SELECT * FROM TBL ORDER BY 日期' DECLARE @MINMISS DATE SET @MINMISS=( SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)), '2012-03-01') AS MISSING FROM TBL A WHERE NOT EXISTS( SELECT * FROM TBL B WHERE B.日期=DATEADD(DD,1,A.日期)) AND EXISTS ( SELECT 1 FROM TBL WHERE 日期='2012-03-01')) PRINT @MINMISS WHILE @MINMISS<=@ENDTIME BEGIN INSERT TBL(日期) VALUES(@MINMISS) SELECT @MINMISS=( SELECT DATEADD(DD,1,MIN(A.日期)) FROM TBL A WHERE NOT EXISTS( SELECT * FROM TBL B WHERE B.日期=DATEADD(DD,1,A.日期)) ) END EXEC(@SQL)EXEC P_SP '2012-03-20'/* 日期 备注 2012-03-01 NULL 2012-03-02 B 2012-03-03 NULL 2012-03-04 NULL 2012-03-05 C 2012-03-06 D 2012-03-07 E 2012-03-08 NULL 2012-03-09 F 2012-03-10 NULL 2012-03-11 G 2012-03-12 H 2012-03-13 I2012-03-14 NULL 2012-03-15 J2012-03-16 NULL2012-03-17 NULL2012-03-18 NULL2012-03-19 K2012-03-20 L */------------------------------------------------------------------------------------------------------方法二,利用递归实现:-->生成测试数据: GO IF OBJECT_ID('TBL')IS NOT NULL DROP TABLE TBL GO CREATE TABLE TBL( 日期 DATE ) GO INSERT TBL SELECT '2012-03-01' UNION ALL SELECT '2012-03-31' --利用递归实现输出三月份的所有日期: go declare @date date select @date=MAX(日期) from tbl ;with t as( select * from tbl union all select dateadd(dd,1,a.日期) from t a where not exists(select * from tbl b where b.日期=DATEADD(DD,1,a.日期) ) and a.日期<@date ) select *from t order by 日期/* 日期 2012-03-01 2012-03-02 2012-03-03 2012-03-04 2012-03-05 2012-03-06 2012-03-07 2012-03-08 2012-03-09 2012-03-10 2012-03-11 2012-03-12 2012-03-13 2012-03-14 2012-03-15 2012-03-16 2012-03-17 2012-03-18 2012-03-19 2012-03-20 2012-03-21 2012-03-22 2012-03-23 2012-03-24 2012-03-25 2012-03-26 2012-03-27 2012-03-28 2012-03-29 2012-03-30 2012-03-31 */--------------------------------------------------- --------------------------------------------------- 方法三:利用系统表构造实现 /* create table #tB( [A] int, [C2] varchar(10), [C3] datetime ) insert #tB select 1,'dfgsdfgsdf','2010-02-01' union all select 2,'dfgsdfgsdf','2010-02-02' union all select 3,'dfgsdfgsdf','2010-02-03' union all select 4,'dfgsdfgsdf','2010-02-04' union all select 4,'dfgsdfgsdf','2010-09-04' union all select 5,'dfgsdfgsdf','2010-09-08' union all select 5,'dfgsdfgsdf','2010-03-08' union all select 6,'dfgsdfgsdf','2010-03-11' union all select 4,'dfgsdfgsdf','2010-05-04' union all select 5,'dfgsdfgsdf','2010-02-08' union all select 6,'dfgsdfgsdf','2010-05-11' union all select 7,'dfgsdfgsdf','2010-05-14' union all select 8,'dfgsdfgsdf','2010-05-16' union all select 7,'dfgsdfgsdf','2010-03-14' union all select 8,'dfgsdfgsdf','2010-03-16' union all select 6,'dfgsdfgsdf','2010-09-11' union all select 7,'dfgsdfgsdf','2010-09-14' union all select 8,'dfgsdfgsdf','2010-09-16' union all select 9,'dfgsdfgsdf','2010-11-17' 想得到如下结果SQL code month total percent 2010-01 0 .... 2010-02 14 .... 2010-03 26 .... 2010-04 0 .... 2010-05 25 .... 2010-06 0 .... 2010-07 0 .... 2010-08 0 .... 2010-09 25 .... 2010-10 0 .... 2010-11 9 .... 2010-12 0 .... */ go if OBJECT_ID('tbl')is not null drop table tbl go create table tbl( [A] int, [C2] varchar(10), [C3] datetime ) insert tbl select 1,'dfgsdfgsdf','2010-02-01' union all select 2,'dfgsdfgsdf','2010-02-02' union all select 3,'dfgsdfgsdf','2010-02-03' union all select 4,'dfgsdfgsdf','2010-02-04' union all select 4,'dfgsdfgsdf','2010-09-04' union all select 5,'dfgsdfgsdf','2010-09-08' union all select 5,'dfgsdfgsdf','2010-03-08' union all select 6,'dfgsdfgsdf','2010-03-11' union all select 4,'dfgsdfgsdf','2010-05-04' union all select 5,'dfgsdfgsdf','2010-02-08' union all select 6,'dfgsdfgsdf','2010-05-11' union all select 7,'dfgsdfgsdf','2010-05-14' union all select 8,'dfgsdfgsdf','2010-05-16' union all select 7,'dfgsdfgsdf','2010-03-14' union all select 8,'dfgsdfgsdf','2010-03-16' union all select 6,'dfgsdfgsdf','2010-09-11' union all select 7,'dfgsdfgsdf','2010-09-14' union all select 8,'dfgsdfgsdf','2010-09-16' union all select 9,'dfgsdfgsdf','2010-11-17' select isnull(c1,'2010-'+right('00'+ltrim(number),2)) as[month],--实现按月份递增 isnull(c2,0) as total, ltrim(cast(isnull(c2,0)*100*1.0/(select sum([A]) fromtbl) as decimal(18,2)))+'%' as [percent] --求百分比 from master..spt_values b left join (select convert(varchar(7),C3,120) as c1,sum([A]) as c2from tbl group by convert(varchar(7),C3,120) ) c on b.number=month(c.c1+'-01') where b.type='p' andb.number between 1 and 12/* month total percent 2010-01 0 0.00% 2010-02 15 14.29% 2010-03 26 24.76% 2010-04 0 0.00% 2010-05 25 23.81% 2010-06 0 0.00% 2010-07 0 0.00% 2010-08 0 0.00% 2010-09 30 28.57% 2010-10 0 0.00% 2010-11 9 8.57% 2010-12 0 0.00%*/
--sql 2000 declare @sdate datetime declare @edate datetime set @sdate = '2002-01-01' set @edate = '2012-01-01' select datepart(yy,dateadd(yy,num,@sdate)) dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where datepart(yy,dateadd(yy,num,@sdate)) <= datepart(yy,@edate)/* dt ----------- 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012(所影响的行数为 11 行) */
--sql 2005 declare @startDate datetime declare @endDate datetimeSELECT @startDate = '2002-01-01' ,@endDate = '2012-01-01' ;WITH tb AS ( SELECT @startDate AS 'date' UNION ALL SELECT DATEADD(yy,1,date) FROM tb WHERE datepart(yy,DATE)<datepart(yy,@endDate) ) SELECT datepart(yy,tb.date) from tb/*----------- 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012(11 行受影响) */
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE
)
GO
INSERT TBL
SELECT '2002-03-01' UNION ALL
SELECT '2012-03-01'
--利用递归实现输出三月份的所有日期:
go
declare @date date
select @date=MAX(日期) from tbl
;with t
as(
select * from tbl
union all
select dateadd(YY,1,a.日期) from t a
where not exists(select * from tbl b
where b.日期=DATEADD(YY,1,a.日期)
)
and a.日期<@date
)
select convert(varchar(4),日期,120)+'年' as 年份 from t order by 日期/*
年份
2002年
2003年
2004年
2005年
2006年
2007年
2008年
2009年
2010年
2011年
2012年
*/
方法一:创建存储过程实现
--如何用"最小缺失数"实现确实日期的自动补全
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'
GO
IF OBJECT_ID('P_SP')IS NOT NULL
DROP PROC P_SP
GO
CREATE PROC P_SP @ENDTIME DATE
AS
DECLARE @SQL VARCHAR(100)
SET @SQL='SELECT * FROM TBL ORDER BY 日期'
DECLARE @MINMISS DATE
SET @MINMISS=(
SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),
'2012-03-01') AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B
WHERE B.日期=DATEADD(DD,1,A.日期))
AND EXISTS (
SELECT 1 FROM TBL WHERE 日期='2012-03-01'))
PRINT @MINMISS
WHILE @MINMISS<=@ENDTIME
BEGIN
INSERT TBL(日期) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT DATEADD(DD,1,MIN(A.日期))
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B
WHERE B.日期=DATEADD(DD,1,A.日期))
)
END
EXEC(@SQL)EXEC P_SP '2012-03-20'/*
日期 备注
2012-03-01 NULL
2012-03-02 B
2012-03-03 NULL
2012-03-04 NULL
2012-03-05 C
2012-03-06 D
2012-03-07 E
2012-03-08 NULL
2012-03-09 F
2012-03-10 NULL
2012-03-11 G
2012-03-12 H
2012-03-13 I2012-03-14 NULL
2012-03-15 J2012-03-16 NULL2012-03-17 NULL2012-03-18 NULL2012-03-19 K2012-03-20 L
*/------------------------------------------------------------------------------------------------------方法二,利用递归实现:-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE
)
GO
INSERT TBL
SELECT '2012-03-01' UNION ALL
SELECT '2012-03-31'
--利用递归实现输出三月份的所有日期:
go
declare @date date
select @date=MAX(日期) from tbl
;with t
as(
select * from tbl
union all
select dateadd(dd,1,a.日期) from t a
where not exists(select * from tbl b
where b.日期=DATEADD(DD,1,a.日期)
)
and a.日期<@date
)
select *from t order by 日期/*
日期
2012-03-01
2012-03-02
2012-03-03
2012-03-04
2012-03-05
2012-03-06
2012-03-07
2012-03-08
2012-03-09
2012-03-10
2012-03-11
2012-03-12
2012-03-13
2012-03-14
2012-03-15
2012-03-16
2012-03-17
2012-03-18
2012-03-19
2012-03-20
2012-03-21
2012-03-22
2012-03-23
2012-03-24
2012-03-25
2012-03-26
2012-03-27
2012-03-28
2012-03-29
2012-03-30
2012-03-31
*/---------------------------------------------------
---------------------------------------------------
方法三:利用系统表构造实现
/*
create table #tB(
[A] int,
[C2] varchar(10),
[C3] datetime
)
insert #tB
select 1,'dfgsdfgsdf','2010-02-01' union all
select 2,'dfgsdfgsdf','2010-02-02' union all
select 3,'dfgsdfgsdf','2010-02-03' union all
select 4,'dfgsdfgsdf','2010-02-04' union all
select 4,'dfgsdfgsdf','2010-09-04' union all
select 5,'dfgsdfgsdf','2010-09-08' union all
select 5,'dfgsdfgsdf','2010-03-08' union all
select 6,'dfgsdfgsdf','2010-03-11' union all
select 4,'dfgsdfgsdf','2010-05-04' union all
select 5,'dfgsdfgsdf','2010-02-08' union all
select 6,'dfgsdfgsdf','2010-05-11' union all
select 7,'dfgsdfgsdf','2010-05-14' union all
select 8,'dfgsdfgsdf','2010-05-16' union all
select 7,'dfgsdfgsdf','2010-03-14' union all
select 8,'dfgsdfgsdf','2010-03-16' union all
select 6,'dfgsdfgsdf','2010-09-11' union all
select 7,'dfgsdfgsdf','2010-09-14' union all
select 8,'dfgsdfgsdf','2010-09-16' union all
select 9,'dfgsdfgsdf','2010-11-17'
想得到如下结果SQL code month total percent
2010-01 0 ....
2010-02 14 ....
2010-03 26 ....
2010-04 0 ....
2010-05 25 ....
2010-06 0 ....
2010-07 0 ....
2010-08 0 ....
2010-09 25 ....
2010-10 0 ....
2010-11 9 ....
2010-12 0 ....
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
[A] int,
[C2] varchar(10),
[C3] datetime
)
insert tbl
select 1,'dfgsdfgsdf','2010-02-01' union all
select 2,'dfgsdfgsdf','2010-02-02' union all
select 3,'dfgsdfgsdf','2010-02-03' union all
select 4,'dfgsdfgsdf','2010-02-04' union all
select 4,'dfgsdfgsdf','2010-09-04' union all
select 5,'dfgsdfgsdf','2010-09-08' union all
select 5,'dfgsdfgsdf','2010-03-08' union all
select 6,'dfgsdfgsdf','2010-03-11' union all
select 4,'dfgsdfgsdf','2010-05-04' union all
select 5,'dfgsdfgsdf','2010-02-08' union all
select 6,'dfgsdfgsdf','2010-05-11' union all
select 7,'dfgsdfgsdf','2010-05-14' union all
select 8,'dfgsdfgsdf','2010-05-16' union all
select 7,'dfgsdfgsdf','2010-03-14' union all
select 8,'dfgsdfgsdf','2010-03-16' union all
select 6,'dfgsdfgsdf','2010-09-11' union all
select 7,'dfgsdfgsdf','2010-09-14' union all
select 8,'dfgsdfgsdf','2010-09-16' union all
select 9,'dfgsdfgsdf','2010-11-17'
select
isnull(c1,'2010-'+right('00'+ltrim(number),2)) as[month],--实现按月份递增
isnull(c2,0) as total,
ltrim(cast(isnull(c2,0)*100*1.0/(select sum([A]) fromtbl) as decimal(18,2)))+'%' as [percent]
--求百分比
from master..spt_values b
left join
(select convert(varchar(7),C3,120) as c1,sum([A]) as c2from tbl
group by convert(varchar(7),C3,120)
) c on b.number=month(c.c1+'-01') where b.type='p' andb.number between 1 and 12/*
month total percent
2010-01 0 0.00%
2010-02 15 14.29%
2010-03 26 24.76%
2010-04 0 0.00%
2010-05 25 23.81%
2010-06 0 0.00%
2010-07 0 0.00%
2010-08 0 0.00%
2010-09 30 28.57%
2010-10 0 0.00%
2010-11 9 8.57%
2010-12 0 0.00%*/
declare @sdate datetime
declare @edate datetime
set @sdate = '2002-01-01'
set @edate = '2012-01-01'
select
datepart(yy,dateadd(yy,num,@sdate)) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
datepart(yy,dateadd(yy,num,@sdate)) <= datepart(yy,@edate)/*
dt
-----------
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012(所影响的行数为 11 行)
*/
declare @startDate datetime
declare @endDate datetimeSELECT @startDate = '2002-01-01' ,@endDate = '2012-01-01'
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(yy,1,date) FROM tb WHERE datepart(yy,DATE)<datepart(yy,@endDate)
)
SELECT datepart(yy,tb.date) from tb/*-----------
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012(11 行受影响)
*/