SQLServer数据库中master..spt_values的应用-职坐标

SQLServer数据库中master..spt_values的应用

小标 2018-07-06 来源：阅读 1886 评论 0

摘要：本文主要向大家介绍了SQLServer数据库中master..spt_values的应用，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

本文主要向大家介绍了SQLServer数据库中master..spt_values的应用，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

今天在做数据分析报表的时候遇到一个这样的问题。

表结构如下。
部门编码、部门名称、部门人员ID（中间用逗号分割）

SQLServer数据库中master..spt_values的应用

我想通过和人员表链接，查询出一个新的数据集，查询出的结果集格式如下：
人员信息（ID或者姓名）、部门编码、部门名称

以前都是通过程序遍历拆分表字段组成新的集合字段，然后在结合SQL语句查询出结果集，但是这个报表要求只能通过SQL语句实现，以前记得可以通过写字段分割函数再结合游标实现。然而今天在网上无意间找到一个新的方法。用“master..spt_values”来实现，具体实现方法见下面实例1感觉这个东西太好用了。把网上的实例都整理了一下，希望各路大神批评指教，也希望大家继续把这方面的应用贴上.

[sql] view plain copy

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

2. /**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/

5. --1.将字符串转换为列显示

7. if object_id('tb') is not null drop table tb

8. go

9. create table tb([编号] varchar(3),[产品] varchar(2),[数量] int,[单价] int,[金额] int,[序列号] varchar(8))

10. insert into tb([编号],[产品],[数量],[单价],[金额],[序列号])

11. select '001','AA',3,5,15,'12,13,14' union all

12. select '002','BB',8,9,13,'22,23,24'

13. go

14. select [编号],[产品],[数量],[单价],[金额]

15. ,substring([序列号],b.number,charindex(',',[序列号]+',',b.number)-b.number) as [序列号]

16. from tb a with(nolock),master..spt_values b with(nolock)

17. where b.number>=1 and b.number<len(a.[序列号]) and b.type='P'

18. and substring(','+[序列号],number,1)=','

19. go

20. drop table tb

21. go

22. /**

23. 编号产品数量单价金额序列号

24. ---- ---- ----------- ----------- ----------- --------

25. 001 AA 3 5 15 12

26. 001 AA 3 5 15 13

27. 001 AA 3 5 15 14

28. 002 BB 8 9 13 22

29. 002 BB 8 9 13 23

30. 002 BB 8 9 13 24

31. */

32. ----------

33.

34. --2.第四个逗号之前的字符串

35. declare @str varchar(100)

36. set @str='10,102,10254,103265,541,2154,41,156'

37. ;with cte as(

38. select left(@str,number-1) as ss,row_number()over(order by getdate()) as xh

39. from master..spt_values with(nolock)

40. where number>=1 and number<=len(@str+',') and type='P'

41. and substring(@str+',',number,1)=','

42. )select ss from cte where xh=4

43. /**

44. ss

45. -------------------

46. 10,102,10254,103265

47. */

48. ----------

49.

50.

51. --3.找出两句话中相同的汉字

52. declare @Lctext1 varchar(100)

53. declare @Lctext2 varchar(100)

54. set @Lctext1='我们都是来自五湖四海的朋友'

55. set @Lctext2='朋友多了路真的好走吗'

56. select substring(@Lctext2,number,1) as value

57. from master..spt_values with(nolock)

58. where type='P' and number>=1 and number<=len(@Lctext2)

59. and charindex(substring(@Lctext2,number,1),@Lctext1,number)>1

60. /**

61. value

62. -----

63. 朋

64. 友

65. 的

66. */

67. ---------

68.

69.

70. --4.提取两个日期之间的所有月份

71. if object_id('tb') is not null drop table tb

72. go

73. create table tb(id int identity(1,1),startDate varchar(10),endDate varchar(10))

74. insert into tb(startDate,endDate) select '2013-01-01','2013-09-25'

75. go

76. declare @startDate varchar(10)

77. declare @endDate varchar(10)

78. select @startDate=startDate,@endDate=endDate from tb with(nolock)

79. select convert(varchar(7),dateadd(mm,number,@startDate),120) as [月份]

80. from master..spt_values with(nolock)

81. where type='P' and number>=0

82. and dateadd(mm,number,@startDate)<=@endDate

83. go

84. drop table tb

85. go

86. /**

87. 月份

88. -------

89. 2013-01

90. 2013-02

91. 2013-03

92. 2013-04

93. 2013-05

94. 2013-06

95. 2013-07

96. 2013-08

97. 2013-09

98. */

99. ---------

100.

101.

102. --5.求一个日期所在月份的所有日期

103. declare @date datetime

104. set @date='2013-08-31'

105. select convert(char(7),@date,120)+'-'+right('0'+convert(varchar(2),number),2) as [日期格式1]

106. ,ltrim(year(@date))+right(100+month(@date),2)+right('0'+ltrim(number),2) as [日期格式2]

107. from master..spt_values with(nolock)

108. where type='P' and number>=1

109. --and number<=datediff(dd,@date,dateadd(mm,1,@date)) --对于mssql而言该语句不试用于2013-08-31的情况，这时由于9月没有31号，固计算出来的天数是30天

110. and number<=datediff(dd,convert(char(7),@date,120)+'-01',convert(char(7),dateadd(mm,1,@date),120)+'-01')--转换为1号来计算天数

111. /**

112. 日期格式1 日期格式2

113. ----------- --------------------

114. 2013-08-01 20130801

115. 2013-08-02 20130802

116. 2013-08-03 20130803

117. 2013-08-04 20130804

118. 2013-08-05 20130805

119. 2013-08-06 20130806

120. 2013-08-07 20130807

121. 2013-08-08 20130808

122. 2013-08-09 20130809

123. 2013-08-10 20130810

124. 2013-08-11 20130811

125. 2013-08-12 20130812

126. 2013-08-13 20130813

127. 2013-08-14 20130814

128. 2013-08-15 20130815

129. 2013-08-16 20130816

130. 2013-08-17 20130817

131. 2013-08-18 20130818

132. 2013-08-19 20130819

133. 2013-08-20 20130820

134. 2013-08-21 20130821

135. 2013-08-22 20130822

136. 2013-08-23 20130823

137. 2013-08-24 20130824

138. 2013-08-25 20130825

139. 2013-08-26 20130826

140. 2013-08-27 20130827

141. 2013-08-28 20130828

142. 2013-08-29 20130829

143. 2013-08-30 20130830

144. 2013-08-31 20130831

145. */

146. ---------

147.

148.

149. --6.根据给定时间为基准以2小时为划分，得出一天划分出的时间段

150. declare @time varchar(5)

151. set @time='11:13'

152. select ltrim(a.number)+right(@time,3)+'-'+ltrim(b.number)+right(@time,3) as [划分结果]

153. from master..spt_values a with(nolock),master..spt_values b with(nolock)

154. where a.type='P' and b.type='P'

155. and a.number>=left(@time,2) and b.number<=24

156. and a.number+2=b.number

157. /**

158. 划分结果

159. -----------------------------------

160. 11:13-13:13

161. 12:13-14:13

162. 13:13-15:13

163. 14:13-16:13

164. 15:13-17:13

165. 16:13-18:13

166. 17:13-19:13

167. 18:13-20:13

168. 19:13-21:13

169. 20:13-22:13

170. 21:13-23:13

171. 22:13-24:13

172. */

173. ---------

174.

175.

176. --7.将字符串显示为行列

177. if object_id('tb') is not null drop table tb

178. create table tb(id int identity(1,1),s nvarchar(100))

179. insert into tb(s) select '车位地址1,车位状况1|车位地址2,车位状况2|车位地址n,车位状况n'

180. ;with cte as(

181. select substring(s,number,charindex('|',s+'|',number)-number) as ss

182. from tb with(nolock),master..spt_values with(nolock)

183. where type='P' and number>=1 and number<=len(s)

184. and substring('|'+s,number,1)='|'

185. )select left(ss,charindex(',',ss)-1)as s1,substring(ss,charindex(',',ss)+1,len(ss))as s2 from cte

186. drop table tb

187. /**

188. s1 s2

189. ----------- ------------

190. 车位地址1 车位状况1

191. 车位地址2 车位状况2

192. 车位地址n 车位状况n

193. */

本文由职坐标整理并发布，希望对同学们学习SQL Server有所帮助，更多内容请关注职坐标数据库SQL Server数据库频道！

sql数据库入门 sqlserver2005 sqlserver数据库入门教程

本文由 @小标发布于职坐标。未经许可，禁止转载。

喜欢 | 0

不喜欢 | 0

看完这篇文章有何感觉？已经有0人表态，0%的人喜欢快给朋友分享吧~

后参与评论

加入IT交流圈

JAVA工程师交流群 +加入

大数据架构师交流群 +加入

人工智能Python交流群 +加入

WEB/H5前端交流群 +加入

数据库 直通车

索取资料
答疑解惑
技术交流
职业测评
面试技巧
高薪秘笈

海同师资推荐更多>>

黄骈联系TA

认证System Analyst，System Architect

程光淼联系TA

精通C、C++等语言、智能芯片开发

热门就业培训班更多>>

【零基础】人工智能+Python全栈开发课程

SQL Server30天热搜词更多>>

sqlserver备份数据库 sqlserver2008 恢复sqlserver系统数据库 sql数据库系统设计 sql数据库入门 sql数据库有哪些 sqlserver数据库开发 sqlserver数据库端口 sqlserver数据库教程 sqlserver数据库入门教程 sql数据库毕业设计 mysql数据库入门教程 sql数据库基础教程 sql数据库怎么启动 sqlserver创建数据库 sqlserver数据库介绍 sqlserver2005 sqlserver数据库还原 sqlserver数据库设计 sql数据库设计工具