SQL Server 中master..spt_values的应用

2023-11-09 来源：华佗小知识

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

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

技术分享

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

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

按 Ctrl+C 复制代码

select number from master..spt_values with(nolock) where type=‘P‘/**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/ --1.将字符串转换为列显示 if object_id(‘tb‘) is not null drop table tb go create table tb([编号] varchar(3),[产品] varchar(2),[数量] int,[单价] int,[金额] int,[序列号] varchar(8)) insert into tb([编号],[产品],[数量],[单价],[金额],[序列号]) select ‘001‘,‘AA‘,3,5,15,‘12,13,14‘ union allselect ‘002‘,‘BB‘,8,9,13,‘22,23,24‘go select [编号],[产品],[数量],[单价],[金额] ,substring([序列号],b.number,charindex(‘,‘,[序列号]+‘,‘,b.number)-b.number) as [序列号] from tb a with(nolock),master..spt_values b with(nolock) where b.number>=1 and b.number<len(a.[序列号]) and b.type=‘P‘and substring(‘,‘+[序列号],number,1)=‘,‘go drop table tb go /** 编号 产品 数量 单价 金额 序列号 ---- ---- ----------- ----------- ----------- -------- AA 3 5 15 12 AA 3 5 15 13 AA 3 5 15 14 BB 8 9 13 22 BB 8 9 13 23 BB 8 9 13 24 */ ---------- --2.第四个逗号之前的字符串 declare @str varchar(100) set @str=‘10,102,10254,103265,541,2154,41,156‘;with cte as( select left(@str,number-1) as ss,row_number()over(order by getdate()) as xh from master..spt_values with(nolock) where number>=1 and number<=len(@str+‘,‘) and type=‘P‘ and substring(@str+‘,‘,number,1)=‘,‘)select ss from cte where xh=4 /** ss ------------------- 10,102,10254,103265 */ ---------- --3.找出两句话中相同的汉字 declare @Lctext1 varchar(100) declare @Lctext2 varchar(100) set @Lctext1=‘我们都是来自五湖四海的朋友‘set @Lctext2=‘朋友多了路真的好走吗‘select substring(@Lctext2,number,1) as value from master..spt_values with(nolock) where type=‘P‘ and number>=1 and number<=len(@Lctext2) and charindex(substring(@Lctext2,number,1),@Lctext1,number)>1 /** value ----- 朋 友 的 */ --------- --4.提取两个日期之间的所有月份 if object_id(‘tb‘) is not null drop table tb go create table tb(id int identity(1,1),startDate varchar(10),endDate varchar(10)) insert into tb(startDate,endDate) select ‘2013-01-01‘,‘2013-09-25‘go declare @startDate varchar(10) declare @endDate varchar(10) select @startDate=startDate,@endDate=endDate from tb with(nolock) select convert(varchar(7),dateadd(mm,number,@startDate),120) as [月份] from master..spt_values with(nolock) where type=‘P‘ and number>=0 and dateadd(mm,number,@startDate)<=@endDate go drop table tb go /** 月份 ------- 2013-01 2013-02 2013-03 2013-04 2013-05 2013-06 2013-07 2013-08 2013-09 */ --------- --5.求一个日期所在月份的所有日期 declare @date datetime set @date=‘2013-08-31‘select convert(char(7),@date,120)+‘-‘+right(‘0‘+convert(varchar(2),number),2) as [日期格式1] ,ltrim(year(@date))+right(100+month(@date),2)+right(‘0‘+ltrim(number),2) as [日期格式2] from master..spt_values with(nolock) where type=‘P‘ and number>=1 --and number<=datediff(dd,@date,dateadd(mm,1,@date)) --对于mssql而言该语句不试用于2013-08-31的情况，这时由于9月没有31号，固计算出来的天数是30天 and number<=datediff(dd,convert(char(7),@date,120)+‘-01‘,convert(char(7),dateadd(mm,1,@date),120)+‘-01‘)--转换为1号来计算天数 /** 日期格式1 日期格式2 ----------- -------------------- 2013-08-01 20130801 2013-08-02 20130802 2013-08-03 20130803 2013-08-04 20130804 2013-08-05 20130805 2013-08-06 20130806 2013-08-07 20130807 2013-08-08 20130808 2013-08-09 20130809 2013-08-10 20130810 2013-08-11 20130811 2013-08-12 20130812 2013-08-13 20130813 2013-08-14 20130814 2013-08-15 20130815 2013-08-16 20130816 2013-08-17 20130817 2013-08-18 20130818 2013-08-19 20130819 2013-08-20 20130820 2013-08-21 20130821 2013-08-22 20130822 2013-08-23 20130823 2013-08-24 20130824 2013-08-25 20130825 2013-08-26 20130826 2013-08-27 20130827 2013-08-28 20130828 2013-08-29 20130829 2013-08-30 20130830 2013-08-31 20130831 */ --------- --6.根据给定时间为基准以2小时为划分，得出一天划分出的时间段 declare @time varchar(5) set @time=‘11:13‘select ltrim(a.number)+right(@time,3)+‘-‘+ltrim(b.number)+right(@time,3) as [划分结果] from master..spt_values a with(nolock),master..spt_values b with(nolock) where a.type=‘P‘ and b.type=‘P‘and a.number>=left(@time,2) and b.number<=24 and a.number+2=b.number /** 划分结果 ----------------------------------- 11:13-13:13 12:13-14:13 13:13-15:13 14:13-16:13 15:13-17:13 16:13-18:13 17:13-19:13 18:13-20:13 19:13-21:13 20:13-22:13 21:13-23:13 22:13-24:13 */ --------- --7.将字符串显示为行列 if object_id(‘tb‘) is not null drop table tb create table tb(id int identity(1,1),s nvarchar(100)) insert into tb(s) select ‘车位地址1,车位状况1|车位地址2,车位状况2|车位地址n,车位状况n‘;with cte as( select substring(s,number,charindex(‘|‘,s+‘|‘,number)-number) as ss from tb with(nolock),master..spt_values with(nolock) where type=‘P‘ and number>=1 and number<=len(s) and substring(‘|‘+s,number,1)=‘|‘)select left(ss,charindex(‘,‘,ss)-1)as s1,substring(ss,charindex(‘,‘,ss)+1,len(ss))as s2 from cte drop table tb /** s1 s2 ----------- ------------ 车位地址1 车位状况1 车位地址2 车位状况2 车位地址n 车位状况n */

按 Ctrl+C 复制代码

SQL Server 中master..spt_values的应用

标签：

小编还为您整理了以下内容，可能对您也有帮助：

请教大家一个关于Sql Server问题,在线等,谢谢。

可以借助master..spt_values来达到你的要求。

master..spt_values：其中类型P较为特殊，它只是0-2047(与版本有关)之间的数字的简单列表，作为对所有类型之间关系的预测。

语句如下：

DECLARE@SearchYear VARCHAR(4) --查询年份

DECLARE@YearFirstDay VARCHAR(10) --查询年份第一天

DECLARE@YearLastDay VARCHAR(10) --查询年份最后一天

SET @SearchYear = '2022'

SET @YearFirstDay = @SearchYear + '-01-01'

SET @YearLastDay = @SearchYear + '-12-31'

SELECT distinct CONVERT(NVARCHAR(10), DateAdd(MONTH,number,@YearFirstDay),120) AS GroupMouth

FROM master..spt_values a CROSS JOIN Employee b

WHERE a.type = 'P' AND a.number <= DateDiff(MONTH,@YearFirstDay,@YearLastDay)

AND @YearFirstDay BETWEEN b.BeginDate AND ISNULL(b.EndDate,'9999-12-31')

ORDER BY GroupMouth

当然前面的DECLARE定义和SET赋值完全可以转换成你前端处理，后端处理主要是Select一段的逻辑。

请教大家一个关于Sql Server问题,在线等,谢谢。

可以借助master..spt_values来达到你的要求。

master..spt_values：其中类型P较为特殊，它只是0-2047(与版本有关)之间的数字的简单列表，作为对所有类型之间关系的预测。

语句如下：

DECLARE@SearchYear VARCHAR(4) --查询年份

DECLARE@YearFirstDay VARCHAR(10) --查询年份第一天

DECLARE@YearLastDay VARCHAR(10) --查询年份最后一天

SET @SearchYear = '2022'

SET @YearFirstDay = @SearchYear + '-01-01'

SET @YearLastDay = @SearchYear + '-12-31'

SELECT distinct CONVERT(NVARCHAR(10), DateAdd(MONTH,number,@YearFirstDay),120) AS GroupMouth

FROM master..spt_values a CROSS JOIN Employee b

WHERE a.type = 'P' AND a.number <= DateDiff(MONTH,@YearFirstDay,@YearLastDay)

AND @YearFirstDay BETWEEN b.BeginDate AND ISNULL(b.EndDate,'9999-12-31')

ORDER BY GroupMouth

当然前面的DECLARE定义和SET赋值完全可以转换成你前端处理，后端处理主要是Select一段的逻辑。

数据库循环插入数值

数量多的话用insert select方法插入，如果只是纯数字的话，可以用系统表

insert into tb (a,b) select a.number,b.number from master..spt_values a,master..spt_values b

where a.type='p' and b.type='p' and a.number between 1 and 100 and b.number between 1 and 5

显示全文

全部频道

SQL Server 中master..spt_values的应用