开发报表月连续出月份

sql开发中经常需要使用数字或者时间的常量表。

比如，输出一年的月份表，输出1000以内的自然数等等。
数量连续且不超过2048。那么使用master..spt_values表就会再也方便不过了。

例如

输出1000以内的自然数：

select number from master..spt_values
where type='P' and 
number between 1 and 1000
--master..spt_values表的type字段值为p的，对应，number字段的值是从1至255

输出2008年至今以来的月份列表：

create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GO

DECLARE 
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='200801',
@EndMonth='200906'

INSERT Mon(Mon)
SELECT
CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)
FROM 
master..spt_values 
WHERE 
type='P' 
and 
DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'

SELECT * FROM Mon

DROP TABLE Mon

---
--
--22222222222222222
--
--

利用MASTER..SPT_VALUES提取两个日期之间的日期列表
2009-11-03 23:47
假设 table 中有一条数据： 
Id DateStart DateEnd 
1 2002-4-1    2009-9-10
提取DateStart到DateEnd之间的年月列表

实现方法：
DECLARE @BDATE DATETIME,@EDATE DATETIME--声明两个日期变量存放DataStart和DataEnd。 
SET @BDATE='2002-4-1'
SET @EDATE='2009-9-10'
--DATEADD(),第一个参数是判断取年、年月、年月日列表。Year——年，MONTH-年月，DAY年月日,再换成年，日时注意修改VARCHAR()的长度。
SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,NUMBER,@BDATE),120) FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(MONTH,NUMBER,@BDATE)<=@EDATE

注：在使用SPT_VALUES方法来获取日期列表时，有一点需要注意，数量不能超过2048，因为NUMBER（TYPE='P'）的范围在0-2047。
也可以使用这个表来获取0-2047之间的数字了。例如取1-1000之间的数字，就可以这么写：
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE NUMBER<1001 AND NUMBER>0 AND TYPE='P'

 

2) 找出2句话中相同的汉字：

declare @Lctext1 varchar(100),@Lctext2 varchar(100)
set @Lctext1='我们都是来自五湖四海的朋友' 
set @Lctext2='朋友多了路真的好走吗'

SELECT SUBSTRING(@Lctext2,number,1) as value
from master..spt_values
where type='p' and number <=LEN(@Lctext2)and CHARINDEX(SUBSTRING(@Lctext2,number,1),@Lctext1)>0

解释：master..spt_values表的type字段值为p的，对应，number字段的值是从1至255，

则where条件中，会找到所有满足条件的number值，1行只有1个numbe值，故select的结果形式是：

1

2

7

 

3) 巧用master..spt_values表输出数字或者时间常量表
2009年08月06日 星期四 上午 11:23
sql开发中经常需要使用数字或者时间的常量表。

比如，输出一年的月份表，输出1000以内的自然数等等。数量连续且不超过2048。那么使用master..spt_values表就会再也方便不过了。

例如

输出1000以内的自然数：

select number from master..spt_values
where type='P' and 
number between 1 and 1000

输出2008年至今以来的月份列表：

create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GO

DECLARE 
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='200801',
@EndMonth='200906'

INSERT Mon(Mon)
SELECT
CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)
FROM 
master..spt_values 
WHERE 
type='P' 
and 
DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'
SELECT * FROM Mon

DROP TABLE Mon

实例：

create table 销售表(id int, name char(1), createDate datetime, sale int)
go


insert into 销售表
select 1, 'A', '2011-11-11 12:10:10', 2 union all
select 2, 'A', '2011-11-12 13:10:10', 4 union all
select 3, 'A', '2011-11-14 13:10:10', 4 union all
select 4, 'B', '2011-11-14 13:10:10', 4 union all
select 5, 'B', '2011-11-18 13:10:10', 4
go


create table 人员表(id int, name char(1)) 
go 


insert into 人员表
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C'

生成报表数据：

1、select t1.createDate,t1.name,sale=isnull(sale,0)from

(select createDate=convert(varchar(10),cast(convert(varchar(7),getdate(),120)+'-'+cast(numberasvarchar)asdatetime),120),b.namefrom master..spt_values a,人员表 b 

where type='p'andnumberbetween1andday(dateadd(dd,-day(dateadd(mm,1,getdate())),dateadd(mm,1,getdate())))) t1 

--生成连续天数

leftjoin 销售表 t2on t1.name=t2.nameand t1.createDate=convert(varchar(10),t2.createDate,120)

2、select m.name , n.dt , isnull(t.sale,0) sale from 人员表 m 

crossjoin(selectdateadd(dd,num,convert(varchar(8),getdate(),120)+'01') dtfrom (selectisnull((selectcount(1) 

from sysobjects where id<t.id),0)as num 

from sysobjects t) awheredateadd(dd,num,convert(varchar(8),getdate(),120)+'01')<=dateadd(mm,1,convert(varchar(8),getdate(),120)+'01')-1) n

leftjoin 销售表 ton m.name= t.nameanddatediff(dd,n.dt,t.createDate)=0orderby n.dt , m.namedroptable 销售表 , 人员表

结果：

name dt sale ---- ------------------------------------------------------ ----------- A 2011-11-01 00:00:00.000 0B 2011-11-01 00:00:00.000 0C 2011-11-01 00:00:00.000 0A 2011-11-02 00:00:00.000 0B 2011-11-02 00:00:00.000 0C 2011-11-02 00:00:00.000 0A 2011-11-03 00:00:00.000 0B 2011-11-03 00:00:00.000 0C 2011-11-03 00:00:00.000 0A 2011-11-04 00:00:00.000 0B 2011-11-04 00:00:00.000 0C 2011-11-04 00:00:00.000 0A 2011-11-05 00:00:00.000 0B 2011-11-05 00:00:00.000 0C 2011-11-05 00:00:00.000 0A 2011-11-06 00:00:00.000 0B 2011-11-06 00:00:00.000 0C 2011-11-06 00:00:00.000 0A 2011-11-07 00:00:00.000 0B 2011-11-07 00:00:00.000 0C 2011-11-07 00:00:00.000 0A 2011-11-08 00:00:00.000 0B 2011-11-08 00:00:00.000 0C 2011-11-08 00:00:00.000 0A 2011-11-09 00:00:00.000 0B 2011-11-09 00:00:00.000 0C 2011-11-09 00:00:00.000 0A 2011-11-10 00:00:00.000 0B 2011-11-10 00:00:00.000 0C 2011-11-10 00:00:00.000 0A 2011-11-11 00:00:00.000 2B 2011-11-11 00:00:00.000 0C 2011-11-11 00:00:00.000 0A 2011-11-12 00:00:00.000 4B 2011-11-12 00:00:00.000 0C 2011-11-12 00:00:00.000 0A 2011-11-13 00:00:00.000 0B 2011-11-13 00:00:00.000 0C 2011-11-13 00:00:00.000 0A 2011-11-14 00:00:00.000 4B 2011-11-14 00:00:00.000 4C 2011-11-14 00:00:00.000 0A 2011-11-15 00:00:00.000 0B 2011-11-15 00:00:00.000 0C 2011-11-15 00:00:00.000 0A 2011-11-16 00:00:00.000 0B 2011-11-16 00:00:00.000 0C 2011-11-16 00:00:00.000 0A 2011-11-17 00:00:00.000 0B 2011-11-17 00:00:00.000 0C 2011-11-17 00:00:00.000 0A 2011-11-18 00:00:00.000 0B 2011-11-18 00:00:00.000 4C 2011-11-18 00:00:00.000 0A 2011-11-19 00:00:00.000 0B 2011-11-19 00:00:00.000 0C 2011-11-19 00:00:00.000 0A 2011-11-20 00:00:00.000 0B 2011-11-20 00:00:00.000 0C 2011-11-20 00:00:00.000 0A 2011-11-21 00:00:00.000 0B 2011-11-21 00:00:00.000 0C 2011-11-21 00:00:00.000 0A 2011-11-22 00:00:00.000 0B 2011-11-22 00:00:00.000 0C 2011-11-22 00:00:00.000 0A 2011-11-23 00:00:00.000 0B 2011-11-23 00:00:00.000 0C 2011-11-23 00:00:00.000 0A 2011-11-24 00:00:00.000 0B 2011-11-24 00:00:00.000 0C 2011-11-24 00:00:00.000 0A 2011-11-25 00:00:00.000 0B 2011-11-25 00:00:00.000 0C 2011-11-25 00:00:00.000 0A 2011-11-26 00:00:00.000 0B 2011-11-26 00:00:00.000 0C 2011-11-26 00:00:00.000 0A 2011-11-27 00:00:00.000 0B 2011-11-27 00:00:00.000 0C 2011-11-27 00:00:00.000 0A 2011-11-28 00:00:00.000 0B 2011-11-28 00:00:00.000 0C 2011-11-28 00:00:00.000 0A 2011-11-29 00:00:00.000 0B 2011-11-29 00:00:00.000 0C 2011-11-29 00:00:00.000 0A 2011-11-30 00:00:00.000 0B 2011-11-30 00:00:00.000 0C 2011-11-30 00:00:00.000 0