在论坛中看到一个帖子,帖子中有一些sql方面的面试题,我觉得这些面试题很有代表性。
原帖的连接为:http://bbs.csdn.net/topics/390884161?page=1#post-398177057
下面是我的解法,供大家参考:
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
- */
- 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
- */
- 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
- */
如(@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.返回表的大小,按照从大到小排序
- 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
写一个存储过程。输入参数@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
- */
在论坛中出现的比较难的sql问题:27(字符串拆分、字符串合并、非连续数字的间隔范围、随机返回字符串)
原文:https://www.cnblogs.com/lonelyxmas/p/12020052.html