问题描述
我想知道Sql server中Tally表的附加功能。
我想将字符串拆分成多个字符串,以下两种方法以相同的方式为我工作。
第一种方法
= ===========
DECLARE @Parameter VARCHAR(8000)
SET @Parameter =',Element01,Element02,Element03,Element04,Element05, '
IF OBJECT_ID('tempdb ..#mytable')IS NOT NULL
DROP TABLE #mytable
SELECT TOP(选择LEN(@)参数))identity(int,1,1)as N INTO #mytable from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
- SET @Parameter =','+ @ Parameter +','
- =====做与循环相同的事情做了......逐步通过变量
- 并返回角色位置和角色...但只有
- 如果它是逗号的那个位置......
SELEC T t1.N,SUBSTRING(@参数,T1.N + 1,CHARINDEX(',',@参数,T1.N + 1)-T1.N-1)val
FROM #mytable T1内连接#mytable t2 on t1.n = t2.n-1
WHERE t1.N< = LEN(@Parameter)
AND SUBSTRING(@Parameter, t1.N,1)=','
订购N
理货表
DECLARE @Parameter VARCHAR(8000)
SET @Parameter =',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('DBO.TALLY')IS NOT NULL
DROP TABLE DBO.TALLY
SELECT TOP(选择LEN(@Parameter))标识(int,1,1)为来自Master.dbo.SysColumns的N INTO dbo.tally sc1,
Master.dbo.SysColumns sc2
- SET @Parameter =','+ @ Parameter +','
- =====做与循环相同的事情......逐步通过变量
- 并返回字符位置和字符..但只有
- 如果它是那个位置的逗号......
SELECT t1.N,SUBSTRING(@ Parameter,T1.N + 1,CHARINDEX(',',@ Parameter,T1.N + 1)-T1.N-1)
来自dbo.Tally T1内部联接dbo.tally t2 on t1.n = t2.n-1
WHERE t1.N< = LEN(@Parameter)
AND SUBSTRING(@参数,t1.N,1)=','
订购N
请指教
谢谢
我的尝试:
tally表和临时表对我来说都是一样的
Hi,
I want to know the additional features of Tally table in Sql server.
I want to split the string into multiple strings for which the below two methods worked for me in the same way.
First Method
============
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('tempdb..#mytable') IS NOT NULL
DROP TABLE #mytable
SELECT TOP (select LEN(@Parameter)) identity(int,1,1)as N INTO #mytable from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--SET @Parameter = ','+@Parameter +','
--===== Do the same thing as the loop did... "Step" through the variable
-- and return the character position and the character... but only
-- if it's a comma in that position...
SELECT t1.N, SUBSTRING(@Parameter,T1.N+1,CHARINDEX(',',@Parameter,T1.N+1)-T1.N-1)val
FROM #mytable T1 inner join #mytable t2 on t1.n=t2.n-1
WHERE t1.N <= LEN(@Parameter)
AND SUBSTRING(@Parameter,t1.N,1) = ','
ORDER BY N
Tally Table
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('DBO.TALLY') IS NOT NULL
DROP TABLE DBO.TALLY
SELECT TOP (select LEN(@Parameter)) identity(int,1,1)as N INTO dbo.tally from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--SET @Parameter = ','+@Parameter +','
--===== Do the same thing as the loop did... "Step" through the variable
-- and return the character position and the character... but only
-- if it's a comma in that position...
SELECT t1.N, SUBSTRING(@Parameter,T1.N+1,CHARINDEX(',',@Parameter,T1.N+1)-T1.N-1)
FROM dbo.Tally T1 inner join dbo.tally t2 on t1.n=t2.n-1
WHERE t1.N <= LEN(@Parameter)
AND SUBSTRING(@Parameter,t1.N,1) = ','
ORDER BY N
Please advise
Thanks
What I have tried:
Both tally table and temp table works same for me
推荐答案
这篇关于SQL Server中的计数表和普通表之间的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!