从时间段生成,学习应用master

代码:

如果要生成的临时表中有个连续的数字列,或者连续的日期列,如下所示:

create table ProductSale(ID int identity(1,1) primary key,ProductName nchar(10), Sales int,Dates datetime)

insert into ProductSale
select  'A',100,'2014-01-05'
UNION ALL
select  'A',200,'2014-02-05'
UNION ALL
select  'A',300,'2014-03-05'
UNION ALL
select  'B',100,'2014-01-05'
UNION ALL
select  'B',300,'2014-03-05'
UNION ALL
select  'B',400,'2014-04-05'

select row_number() over (order by b.年) as ID , b.ProductName,ISNULL(c.Sales,0) Sales,b.Dates from (select rtrim(a.年) '-' rtrim(t.number) '-' rtrim('01') as Dates,a.ProductName,a.年,t.number 月 from (SELECT distinct YEAR(Dates) as 年,ProductName from ProductSale) a,master..spt_values t  where t.type='p' and t.number>=1 and t.number<=12) b left join ProductSale as c on b.年=YEAR(c.Dates) and b.月=MONTH(c.Dates) and b.ProductName=c.ProductName
DECLARE @startDate DATETIME = '2015-09-01'
       ,@endDate DATETIME = '2016-03-01'

--写法一
SELECT CONVERT(NVARCHAR(7),DATEADD(MONTH,number,@startDate),120) 年月
FROM master..spt_values 
WHERE TYPE = 'p'
      AND CONVERT(nvarchar(10),dateadd(MONTH,number,@startDate),120)<=@endDate

--写法二
SELECT CONVERT(NVARCHAR(7),DATEADD(MONTH,number,@startDate),120) 年月
FROM master..spt_values 
WHERE TYPE = 'p'
      AND number<=DATEDIFF(MONTH,@startDate,@endDate)

/*

年月
-------
2015-09
2015-10
2015-11
2015-12
2016-01
2016-02
2016-03

(7 行受影响)

*/

2012-1-1

运行SQL语句之前:

spt_values说明:

2012-1-2

图片 1

  1. master..spt_values是内部字典表,供SQL Server内部使用,许多系统存储过程和函数的源代码中都使用到了它;
  2. 列名分别为名称、值、类型、下限、上限、状态;
  3. 类型列的取值含义:D=Database Option P=Projection DBR=Database Role DC=Database Replication I=Index L=Locks V=Device Type
    因为比较多,无法一一列举。其中类型P较为特殊,它只是0-2047(与版本有关)之间的数字的简单列表,作为对所有类型之间关系的预测。
  4. msdn查不到master..spt_values的说明,不需要太深入了解此表。

2012-1-3

运行SQL语句之后:

 

... ...

图片 2

应用场景:

可以这样写:

知识点

按月份的统计时,若某些月份无数据,统计结果就不会是月份上连续的。可以通过下面方法得到月份连续的结果:

declare @begin datetime,@end datetime
set @begin='2012-1-1'
set @end='2012-1-5'

1、获取日期的年份、月份(GetDate() = '2006-11-08 13:37:56.233')

DECLARE @startDate DATETIME = '2015-09-01'
       ,@endDate DATETIME = '2016-03-01'

SELECT B.年月,ISNULL(A.数量,0) 数量  --无数据的月份,数量设为0
FROM (
        --模拟某个按月份的统考计结果,中间存在无数据的月份
        SELECT '2015-09' 年月, 100 数量
        UNION ALL
        SELECT '2015-12' 年月, 45 数量
        UNION ALL
        SELECT '2016-02' 年月, 78 数量
) A
FULL JOIN 
(
        --用于生成连续的月份
        SELECT CONVERT(NVARCHAR(7),DATEADD(MONTH,number,@startDate),120) 年月
        FROM master..spt_values 
        WHERE TYPE = 'p'
              AND number<=DATEDIFF(MONTH,@startDate,@endDate)
) B ON A.年月 = B.年月
ORDER BY B.年月 '-01'

/*
--月份不连续的结果
年月      数量
------- -----------
2015-09 100
2015-12 45
2016-02 78

(3 行受影响)

------------------------------------------------------------
--月份连续的结果

年月      数量
------- -----------
2015-09 100
2015-10 0
2015-11 0
2015-12 45
2016-01 0
2016-02 78
2016-03 0

(7 行受影响)
*/

declare @days int
set @days=DATEDIFF(dd,@begin,@end)
select DATEADD(dd,number,@begin) 
from master.dbo.spt_values 
where type='p' AND number<=@days

MONTH ( date )返回表示指定日期的“月”部分的整数。SELECT MONTH(GETDATE()) --返回11

 

 

YEAR ( date )返回表示指定日期的“年”部分的整数。SELECT YEAR(GETDATE()) --返回2006

上面的语句中,@begin与@end可以认为是多外面传来的两个参数,我们要求这两个日期之间的日期序列。

2、master..spt_value表的含义

当然,在看上面的代码之前最好先回顾一下master.dbo.spt_values表的数据。

相当于一个数字辅助表(master.dbo.spt_value),master是数据名,spt_value是表名,在sql中主要用到number字段。

select * from master.dbo.spt_values

图片 3

我们使用了其中的type与number两列。

3、sql创建表,自动增长字段IDENTITY

 

语法:IDENTITY(seed,increment) 

spt_values存储的是sybase的系统值。

参数:seed 加载到表中的第一个行所使用的值;

master..spt_values相当于一个数字辅助表,在sql中主要用到number这个字段。

increment 与前一个加载的行的标识值想家的增量值。

select number ``from master..spt_values ``where type=``'p'

create table ProductSale(ID int identity(1,1) primary key)

--这样查询一下就知道什么意思了

4、连接字段

 

如果字段都是CHAR型,直接用“ ”连接;如果地段都是INT型,用CASE函数转换下,即case(字段名 as varchar(4) ),rtrim()函数,将字符串尾部的空格去掉。

 

图片 4图片 5

相对固定通用的取数字的表
主要作用就是取连续数字
不过有个缺陷就是只能取到2047

5、sql语句实现断号问题(ROW_NUMBER函数)

技术内幕系列丛书里面有介绍

row_number() over (order by b.年 ) as  ID

 

 

 

图片 6

这个表貌似是从DB2借用过来的,它存储了一些系统存储过程运行所需要的数据取值范围以前当前值,这个表共有名称,值,类型,最小,最大,状态等六个列,一个约束,一个聚集索引和一个非聚集索引.
网上找不到这个表结构所代表的含义,但从表的数据值来看,可以猜出一些来,比如,类型为B,那就应该是布尔型,它的名称和取值有四种,yes or no,no,yes,none.又如类型I似乎是与索引有关的一些数据名.你也可以通过名称及值的范围来猜出一些.
自然数序列0~2047的name为NULL,类型为p,猜不出这个p对应什么英文单词,或许在某个系统存储过程中要用它自然数序列,或许就是对应的这个存储过程名吧.
系统表中的东西,有些是要弄清楚的,而像spt_values这个表,联机丛书中都没有给出说明,那就是说它并不要求你知道它,我们只要知道能拿它来引用(比如类型p的数字序列)就足够了,而且,建议你不要试图去更改这个表的内容,否则可能会出现无法意料的后果. 

参考资料:

 

 http://bbs.csdn.net/topics/390656141

 

 http://s.yanghao.org/program/viewdetail.php?i=347818

可以到安装文件里搜索 u_tables.sql 文件查看spt_values表注释情况

 

如下一段:

SQL code

 

?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,0 ,'P  ' ,1 ,0x00000001 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,1 ,'P  ' ,1 ,0x00000002 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,2 ,'P  ' ,1 ,0x00000004 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,3 ,'P  ' ,1 ,0x00000008 ,0)
 
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,4 ,'P  ' ,1 ,0x00000010 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,5 ,'P  ' ,1 ,0x00000020 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,6 ,'P  ' ,1 ,0x00000040 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,7 ,'P  ' ,1 ,0x00000080 ,0)
 
go
 
-- 'P  ' continued....
declare
     @number_track        integer
    ,@char_number_track    varchar(12)
 
select     @number_track        = 7
select     @char_number_track    = convert(varchar,@number_track)
 
-- max columns is 1024 so we need 1024 bit position rows;
-- we'll actually insert entries for more than that
while @number_track < 1024
    begin
 
    raiserror('type=''P  '' ,@number_track=%d' ,0,1 ,@number_track)
 
    EXECUTE(
    '
    insert spt_values (name ,number ,type ,low ,high ,status)
      select
         null
 
        ,(select     max(c_val.number)
            from     spt_values    c_val
            where     c_val.type = ''P  ''
            and     c_val.number between and '   @char_number_track   '
         )
              a_val.number   1
 
        ,''P  ''
 
        ,(select     max(b_val.low)
            from     spt_values    b_val
            where     b_val.type = ''P  ''
            and     b_val.number between and '   @char_number_track   '
         )
              1   (a_val.number / 8)
 
        ,a_val.high
        ,0
        from
         spt_values    a_val
        where
         a_val.type = ''P  ''
        and     a_val.number between and '   @char_number_track   '
    ')
 
 
    select @number_track = ((@number_track   1) * 2) - 1
    select @char_number_track = convert(varchar,@number_track)
 
    end --loop
go

 

 

 

 

master..spt_values 相当于 master.dbo.spt_values

master 是数据库名
spt_values是表名

spt_values 是一张常量表  系统表

 

该表是从sybase继承过来的,是个内部字典表,供SQL Server内部使用。
我们可以在许多系统存储过程和函数的源代码中发现它的身影。其实可以将它理解成我们编程时常用的数据字典.

列名分别为名称、值、类型、下限、上限、状态;

类型列的取值含义:
D=Database Option P=Projection DBR=Database Role DC=Database Replication I=Index L=Locks V=Device Type
因为比较多,无法一一列举。其中类型P较为特殊,它只是0-2047(与版本有关)之间的数字的简单列表,作为对所有类型之间关系的预测。

 

 

select number from master..spt_values with(nolock) where type='P'
/**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/ 

 

 

 

select MONTH(convert(varchar(10), dateadd(MONTH, number - 1,
DATEADD(yy,DATEDIFF(yy,0,getdate()),0)), 120)) as yue from master.dbo.spt_values WHERE
type='P' AND number <= datediff(MONTH, DATEADD(yy,DATEDIFF(yy,0,getdate()),0),
dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()) 1,0)) ) 1 AND number>0

 

select * from master.dbo.spt_values where type='p'

select number from master..spt_values with(nolock) where type='P'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:从时间段生成,学习应用master

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。