22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jac ob'
INSERT INTO @t(data) SELECT 'Sebastian'
;with cte as
(
select id,cast(right(data,1) as varchar(20)) as data,cast(left(data,len(data)-1) as varchar(20)) as data1
from @t
union all
select id
,cast(data+right(data1,1) as varchar(20)) as data
,cast(left(data1,len(data1)-1) as varchar(20)) as data1
from cte c
where len(data1)>0
)
select id,data from cte a
where len(data1)=0
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
;with cte as
(
select id,cast(right(data,1) as varchar(20)) as data,1 as num from @t
union all
select t.id
,cast(c.data + substring(t.data,len(t.data)-c.num,1) as varchar(20)) as data
,c.num+1 as num
from @t t,cte c
where t.id = c.id and c.num < len(t.data)
)
select id,data from cte a
where not exists (
select 1
from cte b
where b.id = a.id
and b.num > a.num
)
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
;with cte as
(
select id,left(data,1) as data,1 as num from @t
union all
select t.id,substring(t.data,c.num+1,1) as data,c.num+1 as num from @t t,cte c
where t.id = c.id and c.num < len(t.data)
)
select a.id,(
select '' + data from cte
where id = a.id
order by num desc for xml path('')
) as data
from @t a
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
;WITH MU AS (
SELECT T1.*,ROW_NUMBER() OVER(PARTITION BY T1.ID ORDER BY T2.NUMBER DESC) AS NUMBER,SUBSTRING(T1.DATA,T2.NUMBER,1) AS DATA2
FROM @T T1
INNER JOIN MASTER..SPT_VALUES T2 ON LEN(T1.DATA)>=T2.NUMBER AND T2.TYPE='P' AND T2.NUMBER>0
)
,MU2 AS (
SELECT *,CONVERT(VARCHAR(MAX),DATA2) AS DATA3,1 AS LEVEL FROM MU WHERE NUMBER=1
UNION ALL
SELECT MU.*,MU2.DATA3+MU.DATA2,MU2.LEVEL+1
FROM MU
INNER JOIN MU2 ON MU.NUMBER=MU2.NUMBER+1 AND MU.ID=MU2.ID
)
SELECT DATA,DATA3
FROM MU2 T1
WHERE NOT EXISTS(
SELECT 1 FROM MU2 T2 WHERE T2.LEVEL>T1.LEVEL AND T2.ID=T1.ID
)
--2005
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
select fgid=(select ''+name
from
(
select data,substring(a.data,b.number+1,1)name,number from @t a ,master..spt_values b
where type='p' and number<=len(a.data)) tb
where tb.data=ta.data order by number desc for xml path('') )
from @t ta