在论坛中出现的比较难的sql问题：22(字符串拆分、字符串合并、非连续数字的间隔范围、随机返回字符串)

2ewyt

下面是我的解法，供大家参考：


1、分拆字符串



    create table test1 (number varchar(100))  
    insert into test1 values ('1,2,3,4,5,6')  
      
    希望结果:  
      
    number  
    ------  
    1  
    2  
    3  
    4  
    5  
    6  
      
    (6 行受影响)  


我的解法：



    --1.拆分字符串  
    create table test1 (number varchar(100))  
    insert into test1 values ('1,2,3,4,5,6')  
      
      
    select --t.number,  
           SUBSTRING(t.number, s.number ,CHARINDEX(',',t.number+',',s.number)-s.number) as number  
    from test1 t,master..spt_values s  
    where s.number >=1  
    and s.type = 'P'  
    and SUBSTRING(','+t.number,s.number,1) = ','  
    /*  
    number  
    1  
    2  
    3  
    4  
    5  
    6  
    */  


2、非连续数字的范围



    create table test2(number int)  
    insert into test2 values   
    (1),(2),(3),(4),(5),(7),(8),  
    (10),(11),(13),(15)  
      
    实现效果  
    number  
    ---------------------  
    1~5  
    7~8  
    10~11  
    13  
    15  


我的解法：



    --2.非连续数字的间隔  
    create table test2(number int)  
    insert into test2 values   
    (1),(2),(3),(4),(5),(7),(8),  
    (10),(11),(13),(15)  
      
      
    ;with t  
    as  
    (  
    select *,  
           row_number() over(order by number) rn  
    from test2  
    )  
      
    select case when min(number)=max(number) then cast(min(number) as varchar)   
                else cast(min(number) as varchar)+'~'+cast(max(number) as varchar) end as number  
    from t  
    group by number-rn  
    /*  
    number  
    1~5  
    7~8  
    10~11  
    13  
    15  
    */  


3、合并字符串



    create table test4 (name varchar(10),mytype varchar(10),cj int )  
      
    insert into test4   
    values('张三','语文',83),  
    ('张三','数学',65),  
    ('张三','物理',85),  
    ('李四','语文',73),  
    ('李四','数学',69),  
    ('李四','物理',93)  
      
      
    实现效果  
    name    mytype              cj  
    张三  语文，数学，物理    83,65,85  
    李四  语文，数学，物理    73,69,93  


我的解法：



    --3.合并字符串  
    create table test4 (name varchar(10),mytype varchar(10),cj int )  
      
    insert into test4   
    values('张三','语文',83),  
    ('张三','数学',65),  
    ('张三','物理',85),  
    ('李四','语文',73),  
    ('李四','数学',69),  
    ('李四','物理',93)  
      
      
    select name,  
           stuff((select ','+mytype from test4 t4 where t4.name = test4.name for xml path('')),1,1,'') as mytype,  
           stuff((select ','+cast(cj as varchar) from test4 t4 where t4.name = test4.name for xml path('')),1,1,'') as cj  
    from test4  
    group by name  
    /*  
    name    mytype  cj  
    李四  语文,数学,物理    73,69,93  
    张三  语文,数学,物理    83,65,85  
    */  


4、写一个函数，输入2个varchar参数@str1，@str2，返回字符串@str1 在@str2中出现的次数，@int.

如（@str2=’abcsaac',@str1='a'） 返回3。


我的解法：



    --4.串1在串2出现次数  
    declare @str2 varchar(100)='abcsaac'  
    declare @str1 varchar(10)='a'  
      
      
    select (len(@str2) - len(replace(@str2,@str1,''))) / len(@str1) as t  --这里需要除以字符串1的长度  
    /*  
    t  
    3  
    */  


5、查询所有表空间的大小，并按照从大到小排序。


我的解法：



    --5.返回表的大小,按照从大到小排序  
    create table tb1  
    (  
    name varchar(100),  
    rows numeric,  
    reserved varchar(100),  
    data varchar(100),  
    index_size varchar(100),  
    unused varchar(100)  
    )  
      
    exec sp_msforeachtable 'insert into tb1 exec sp_spaceused ''?'''  
      
    select *  
    from tb1  
    order by cast(replace(reserved,' KB','') as numeric) desc   


6、随机返回指定个数的字符

写一个存储过程。输入参数@int,返回随机@int 个字母。如输入5个。返回随机5个字母。


我的解法：



    --6.随机返回指定个数的字符  
    declare @int int   
    declare @str varchar(1000)  
      
    set @int = 5  
    set @str = ''  
      
    select @str = @str + char(ascii('A') + abs(checksum(newid())) % 26)  
    from master..spt_values   
    where type='P'  
    and number between 1 and @int  
      
      
    select @str  
    /*  
    EPOZQ  
    */