学习 SQL 的输出代码

[RICHEER COCA]

执行代码

create function F_strSpit12(@s varchar(200)) 
returns @t table(col varchar(5))
as
begin 
    insert into @t select substring(@s,number,1)
    from spt_values where number <=len(@s)
    while not exists(select 1 from @t where col = @s)
    insert @t 
    SELECT T3.COL + T2.COL FROM @t AS T2,@t AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL
 
    return
end
 go
declare @s varchar(200)
set @s='ABCD'
select distinct col,len(col)    ---INTO #RESULT   执行INTO语句就报错错？
from dbo.F_strSpit12(@s)
where len(col) > 0 
order by len(col),col
 -----select * from  #result
 drop function F_strSpit12

问题：如何输出到 表#result？

希望#result表的格式为

#result表的格式为
*/
col	CON
-----------------------------------
A	1
B	1
C	1
D	1
AB	2
AC	2
AD	2
BC	2
BD	2
CD	2
ABC	3
ABD	3
ACD	3
BCD	3
ABCD	4
 */

[唐诗三百首]

create table #result
(col varchar(5), CON int)

declare @s varchar(200)
set @s='ABCD'

;with t as(
select distinct col,len(col) 'CON'
from dbo.F_strSpit12(@s)
where len(col) > 0)
 insert into #result(col,CON)
 select col,CON
 from t
 order by CON,col

[RICHEER COCA]

引用 1 楼 ap0405140 的回复:

create table #result
(col varchar(5), CON int)

declare @s varchar(200)
set @s='ABCD'

;with t as(
select distinct col,len(col) 'CON'
from dbo.F_strSpit12(@s)
where len(col) > 0)
 insert into #result(col,CON)
 select col,CON
 from t
 order by CON,col

继续请教，#result表的格式为如下，如何改代码？

*/
  id    col	CON
-----------------------------------
1001 A	1
1002 B	1
1003 C	1
1004 D	1
1005 AB	2
1005 AC	2
1007 AD	2
1008 BC	2
1009 BD	2
1010 CD	2
1011 ABC	3
1012 ABD	3
1013 ACD	3
1014 BCD	3
1015 ABCD	4
 */

[RICHEER COCA]

这样对吗？增加一个主键 [ID] [int] IDENTITY(1001,1) NOT NULL,

create table #result
([ID] [int] IDENTITY(1001,1) NOT NULL,col varchar(5), CON int)

declare @s varchar(200)
set @s='ABCD'

;with t as(
select distinct col,len(col) 'CON'
from dbo.F_strSpit12(@s)
where len(col) > 0)
 insert into #result(col,CON)
 select col,CON
 from t
 order by CON,col

[唐诗三百首]

引用 3 楼 u011709039 的回复:

这样对吗？增加一个主键 [ID] [int] IDENTITY(1001,1) NOT NULL,

create table #result
([ID] [int] IDENTITY(1001,1) NOT NULL,col varchar(5), CON int)

declare @s varchar(200)
set @s='ABCD'

;with t as(
select distinct col,len(col) 'CON'
from dbo.F_strSpit12(@s)
where len(col) > 0)
 insert into #result(col,CON)
 select col,CON
 from t
 order by CON,col

是的,