1.master..spt_vlaues 进行拆分 2.row_number()和number差求出的最大最小值 3.for xml path('') 4.用excel中的简单办法,select len('ABCDABAA')-len(replace('ABCDABAA','A','')) 5.用系统视图,从网上查查 6.没有思路,请大侠们指点下呗
这个真心有难度啊, 反正我是一边参考以前收集的小脚本,一边测试,弄个半个多小时 如果是纯手写,我觉得可以放弃了,上机测试还好create table test1 (number varchar(100)) insert into test1 values ('1,2,3,4,5,6')select * from test1 --本来是由函数的 create function dbo.f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql<>'') begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1); insert @temp values(@ch); set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),''); end return end --然后就出来结果了 select * from test1 cross apply dbo.f_splitstr(test1.number,',') number F1 ----------------- ---------------------------------------------------------------------------- 1,2,3,4,5,6 1 1,2,3,4,5,6 2 1,2,3,4,5,6 3 1,2,3,4,5,6 4 1,2,3,4,5,6 5 1,2,3,4,5,6 6(6 行受影响)--实质上就是这个拆分的逻辑 declare @str varchar(max) declare @tmp varchar(10) set @str='1,2,3,4,5,6' set @str=@str+',' while(@str<>'') begin set @tmp=left(@str,charindex(',',@str,1)-1); print @tmp set @str=stuff(@str,1,charindex(',',@str,1),''); endcreate table test2(number int) insert into test2 values (1),(2),(3),(4),(5),(7),(8), (10),(11),(13),(15) with cte as ( select ROW_NUMBER()over(order by number) as ID,cast(number as varchar(10)) as number from test2 ) select case when( MIN(number)=MAX(number)) then MAX(number) else MIN(number) +'~'+MAX(number) end as result from ( select id,number,number-id as flag from cte ) t group by flagresult --------------------- 1~5 7~8 10~11 13 15(5 行受影响) create table test4 (name varchar(10),mytype varchar(10),cj int )insert into test4 values ('张三','语文',83), ('张三','数学',65), ('张三','物理',85), ('李四','语文',73), ('李四','数学',69), ('李四','物理',93) select * from test4; with cte1 as ( SELECT T1.name , ( SELECT T2.mytype + ',' FROM test4 T2 WHERE T2.name = T1.name order by mytype FOR XML PATH('') ) AS ColNames FROM test4 T1 GROUP BY T1.name ), cte2 as ( SELECT T1.name , ( SELECT cast(T2.cj as varchar(10)) + ',' FROM test4 T2 WHERE T2.name = T1.name order by mytype FOR XML PATH('') ) AS ColNames FROM test4 T1 GROUP BY T1.name ) select cte1.name,cte1.ColNames,cte2.ColNames from cte1 inner join cte2 on cte1.name=cte2.name--李四 语文,数学,物理, 73,69,93, --张三 语文,数学,物理, 83,65,85, --4,写一个函数,输入2个varchar参数@str1,@str2,返回字符串@str1 在@str2中出现的次数,@int. --如(@str2=’abcsaac',@str1='a') 返回3. declare @str varchar(20) declare @flag varchar(1) declare @cnt int set @cnt=0 set @str='abcdefabc' set @flag='a' while CHARINDEX(@flag,@str)>0 begin set @str=STUFF(@str,1,charindex(@flag,@str),'') set @cnt=@cnt+1 end print @cnt--查询所有表空间的大小。并按照从大到小排序。sqlserver表的大小吧 --这个有点长啊 --exec sp_spaceused --这个是查询行数的 SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC--随机字符 declare @i int declare @result varchar(max) set @i=10 set @result='' while @i>0 begin set @result=@result+CHAR(RAND()*23+97) set @i=@i-1 end print @result
-- 大概用了 一个半小时,第一感觉挺容易的,真要调通了,还是真不是个省劲的活。 -- 我是 SQL Server R2 的环境。 -- 1. ---- create table test1 (number varchar(100)) insert into test1 values ('1,2,3,4,5,6')with b as (select ROW_NUMBER()over(order by id) rn from sysobjects ) select SUBSTRING(a.number,b.rn * 2 - 1 ,1) from test1 a, b where rn <= 6 -- 2. ---- create table test2(number int) insert into test2 values (1),(2),(3),(4),(5),(7),(8),(10),(11),(13),(15)with b as (select number - ROW_NUMBER() over(order by number) rn, number from test2 ) select case when MIN(number) = MAX(number) then cast(MAX(number) as varchar(10)) else CAST(min(number) as varchar(10)) + '-' + CAST(max(number) as varchar(10)) end from b group by rn--3. ---- create table test4 (name varchar(10),mytype varchar(10),cj int )
insert into test4 values('张三','语文',83),('张三','数学',65),('张三','物理',85), ('李四','语文',73),('李四','数学',69),('李四','物理',93)select name , '语文' , max(case when mytype = '语文' then cj else 0 end), '数学' , max(case when mytype = '数学' then cj else 0 end), '物理' , max(case when mytype = '物理' then cj else 0 end) from test4 group by name --4. ---- create function fn_times(@str1 varchar(10) , @str2 varchar(100)) returns int as begin return LEN(@str2) - LEN(REPLACE( @str2 , @str1 , '')) end go select dbo.fn_times('1','112345') go--5. ---- declare @t table( name varchar(30) , rows int , reserved varchar(40) , data varchar(40) , index_size varchar(40) , unused varchar(40) ) declare @name varchar(60) declare s cursor for select name from sysobjects where xtype = 'u' open s fetch next from s into @name while @@FETCH_STATUS = 0 begin insert into @t exec sp_spaceused @name fetch next from s into @name end close s deallocate s -- 按数据大小排序 select * from @t order by CONVERT(int, REPLACE(data,'KB','')) desc --6. ----create proc p_nono(@plen int , @str varchar(100) output) as begin declare @i int = 0 , @j int declare @result varchar(100) = '' while @i < @plen begin select @i = @i + 1 select @j = RAND() * 37 from (select 1 c )x select @result = @result + char( 65 + @j * @i % 25 ) end set @str = @result end godeclare @var varchar(100) exec p_nono 10,@var output print @var
第一题 declare @str varchar(8000) select @str=' select '+REPLACE(''+number+'',',',' number union all select ') from test1 exec(@str)
--------1 create table #test1 (number varchar(100)) insert into #test1 values ('1,2,3,4,5,6') insert into #test1 values ('1,2,3,4,5,6')SELECT t.c.value('.','int') FROM( SELECT CONVERT(XML,'<x>'+replace(number,',','</x><x>')+'</x>') AS B from #test1) a CROSS APPLY a.B.nodes('/x') T(c) ------------2 create table #test2(number int) insert into #test2(number) values (1)insert into #test2(number) values (2) insert into #test2(number) values (3) insert into #test2(number) values (4) insert into #test2(number) values (5) insert into #test2(number) values (7) insert into #test2(number) values (8) insert into #test2(number) values (10) insert into #test2(number) values (11) insert into #test2(number) values (13) insert into #test2(number) values (15);WITH cte AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS rn FROM #test2 ) ,t AS ( SELECT * ,1 AS partitionId FROM cte WHERE rn=1 UNION ALL SELECT cte.*,CASE WHEN t.number+1=cte.number THEN t.partitionId ELSE t.partitionId+1 END AS partitionId FROM cte ,t WHERE cte.rn=t.rn+1 ) SELECT CASE WHEN MIN(t.number)=MAX(t.number) THEN CAST(MAX(t.number)AS VARCHAR) ELSE CAST(MIN(t.number)AS VARCHAR)+'~'+CAST(MAX(t.number)AS VARCHAR) END FROM t GROUP BY t.partitionId------------3 create table #test4 (name varchar(10),mytype varchar(10),cj int )
insert into #test4 values('张三','语文',83) insert into #test4 values('张三','数学',65) insert into #test4 values('张三','物理',85) insert into #test4 values('李四','语文',73) insert into #test4 values('李四','数学',69) insert into #test4 values('李四','物理',93)SELECT name ,STUFF((SELECT ','+mytype FROM #test4 AS B WHERE A.name=B.name FOR XML PATH('')),1,1,'') ,STUFF((SELECT ','+CAST(cj AS VARCHAR) FROM #test4 AS B WHERE A.name=B.name FOR XML PATH('')),1,1,'') FROM #test4 AS A GROUP BY name--4,写一个函数,输入2个varchar参数@str1,@str2,返回字符串@str1 在@str2中出现的次数,@int. --如(@str2=’abcsaac',@str1='a') 返回3. DECLARE @strA VARCHAR(20) DECLARE @strB VARCHAR(1) SELECT @strA='abcsaac' SELECT @strB='a' SELECT (len(@strA) - len(replace(@strA,@strB,'')))/len(@strB)-----------5,查询所有表空间的大小。并按照从大到小排序。 SELECT '不知道,没写过' ----------6,写一个存储过程。输入参数@int,返回随机@int 个字母。如输入5个。返回随机5个字母。 SELECT '做一张字母常用表,随机数row_number() over(order by newid())去取就行了'
第四题create function pos( @str1 varchar(100), @str2 varchar(100) ) returns int as begin declare @len int; declare @n int; if(len(@str1)>0 and len(@str2)>0) begin set @len=len(@str1); set @n=0; set @n=(len(@str2)-len(replace(@str2,@str1,'')))/@len; end return @n; end
第六题create proc printLetter ( @n int ) as begin declare @i int; set @i=1; while @i<=@n begin print char(cast(ceiling(rand() *25) as int)+64); set @i=@i+1; end endexec printLetter 6
第二题DECLARE ub CURSOR FOR SELECT number FROM test2--打开一个游标 OPEN ub --循环一个游标 DECLARE @num int declare @nfirst varchar(20) declare @nend varchar(20) set @nfirst='' set @nend=''; declare @nlast int set @nlast=0; FETCH NEXT FROM ub INTO @num WHILE @@FETCH_STATUS =0 BEGIN if @nfirst='' begin set @nfirst=CAST(@num as varchar) if @nlast=0 set @nlast=@num; end else begin if @num-@nlast=1 begin set @nend='~'+CAST(@num as varchar) set @nlast=@num end else begin print @nfirst+@nend; set @nfirst=CAST(@num as varchar) set @nend='' set @nlast=@num end end
FETCH NEXT FROM ub INTO @num END print @nfirst+@nend; --关闭游标 CLOSE ub --释放资源 DEALLOCATE ub
这个是我的解法,你可以看看,有些思路是和上面差不多的,不过像第1个问题,就必要用递归了,因为我以前试过3种拆分字符串的方法,而递归是性能较差的一种: --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) ;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) 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.串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 --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
1,用递归。依次判断‘,’ 用patindex找出位置。然后用sustring 进行一次截取,直到
patindex返回0
2,先用递归进行分组。找出那些是连续的应该在一组。这个我感觉貌似可以用APPLY 做。
但是本人对APPLY 不熟悉。没啥经验。想了半天 没想到。希望大哥指导下。
3,用动态SQL 或者XML进行连接字符串。
4,这个我还是用和第一题一样的思路。依次截取@str1,知道返回0终止递归。
5,没思路,网上随便找一个脚本。后面问了一个大神。给了我一个脚本。
6,这个完全没思路。百度了下。貌似用char(65+celing()) 这种来实习。
面试的时候说我前面2个都比较复杂。请各位指点下还有啥简单方法 谢谢
第二题 稍微要复杂一点
第三题 递归也可以解决
第四题我刚好写了一篇博客:http://www.cnblogs.com/chenmh/p/3967913.html
第五题有好几个系统视图都可以,sys.dm_db_partition_stats
第六题我不是很明白你表达的意思,是输入一个输出一个,还是输入五个一个一个的输出,他问的意思是不是队列
with t2 as
(
select ID, number, number -id as diff from (
select ROW_NUMBER ()over(order by number ) as id,number from
test2)t
)
select diff,
(select cast(number as nvarchar(200)) + ',' from t2
WHERE a.diff =diff
for XML PATH(''))as numberlist
from t2 a
group by diff
2.row_number()和number差求出的最大最小值
3.for xml path('')
4.用excel中的简单办法,select len('ABCDABAA')-len(replace('ABCDABAA','A',''))
5.用系统视图,从网上查查
6.没有思路,请大侠们指点下呗
反正我是一边参考以前收集的小脚本,一边测试,弄个半个多小时
如果是纯手写,我觉得可以放弃了,上机测试还好create table test1 (number varchar(100))
insert into test1 values ('1,2,3,4,5,6')select * from test1
--本来是由函数的
create function dbo.f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100))
returns @temp table(F1 varchar(100))
as
begin
declare @ch as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
while(@SourceSql<>'')
begin
set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1);
insert @temp values(@ch);
set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'');
end
return
end
--然后就出来结果了
select * from test1 cross apply dbo.f_splitstr(test1.number,',')
number F1
----------------- ----------------------------------------------------------------------------
1,2,3,4,5,6 1
1,2,3,4,5,6 2
1,2,3,4,5,6 3
1,2,3,4,5,6 4
1,2,3,4,5,6 5
1,2,3,4,5,6 6(6 行受影响)--实质上就是这个拆分的逻辑
declare @str varchar(max)
declare @tmp varchar(10)
set @str='1,2,3,4,5,6'
set @str=@str+','
while(@str<>'')
begin
set @tmp=left(@str,charindex(',',@str,1)-1);
print @tmp
set @str=stuff(@str,1,charindex(',',@str,1),'');
endcreate table test2(number int)
insert into test2 values
(1),(2),(3),(4),(5),(7),(8),
(10),(11),(13),(15)
with cte as
(
select ROW_NUMBER()over(order by number) as ID,cast(number as varchar(10)) as number from test2
)
select
case when( MIN(number)=MAX(number))
then MAX(number)
else
MIN(number) +'~'+MAX(number)
end
as result
from
(
select id,number,number-id as flag from cte
) t group by flagresult
---------------------
1~5
7~8
10~11
13
15(5 行受影响)
create table test4 (name varchar(10),mytype varchar(10),cj int )insert into test4
values
('张三','语文',83),
('张三','数学',65),
('张三','物理',85),
('李四','语文',73),
('李四','数学',69),
('李四','物理',93)
select * from test4;
with cte1 as
(
SELECT T1.name ,
( SELECT T2.mytype + ','
FROM test4 T2
WHERE T2.name = T1.name order by mytype
FOR
XML PATH('')
) AS ColNames
FROM test4 T1
GROUP BY T1.name
),
cte2 as
(
SELECT T1.name ,
( SELECT cast(T2.cj as varchar(10)) + ','
FROM test4 T2
WHERE T2.name = T1.name order by mytype
FOR
XML PATH('')
) AS ColNames
FROM test4 T1
GROUP BY T1.name
)
select cte1.name,cte1.ColNames,cte2.ColNames from cte1 inner join cte2 on cte1.name=cte2.name--李四 语文,数学,物理, 73,69,93,
--张三 语文,数学,物理, 83,65,85,
--4,写一个函数,输入2个varchar参数@str1,@str2,返回字符串@str1 在@str2中出现的次数,@int.
--如(@str2=’abcsaac',@str1='a') 返回3.
declare @str varchar(20)
declare @flag varchar(1)
declare @cnt int
set @cnt=0
set @str='abcdefabc'
set @flag='a'
while CHARINDEX(@flag,@str)>0
begin
set @str=STUFF(@str,1,charindex(@flag,@str),'')
set @cnt=@cnt+1
end
print @cnt--查询所有表空间的大小。并按照从大到小排序。sqlserver表的大小吧
--这个有点长啊
--exec sp_spaceused --这个是查询行数的
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN
sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC--随机字符
declare @i int
declare @result varchar(max)
set @i=10
set @result=''
while @i>0
begin
set @result=@result+CHAR(RAND()*23+97)
set @i=@i-1
end
print @result
--pppoenfvwi
第四题好思路。这里还要注意下@STR1的长度。最后应该还要用差除以len(@str1)
很多没细看 的确是手写的。涂涂改改。写的恶心。人家也没细看。最后问了下思路。
你最后一题。这个char(rand()*23+97) 没问题。但是我测试下。貌似char(rand()*64+65) 都可以啊 他这个是根据取随机小数。然后处理的。是不是*23+97 差不多保证每个字母的概率是随机的。
a
select CHAR(122)
z
那这个不是最大结果就是char(22.99+97) 肯定小于120,最后3个字母不是取不到吗?
那这个不是最大结果就是char(22.99+97) 肯定小于120,最后3个字母不是取不到吗?这个我也没算清楚,就是临时试出来的,可以全面综合考虑一下。
-- 我是 SQL Server R2 的环境。
-- 1. ----
create table test1 (number varchar(100))
insert into test1 values ('1,2,3,4,5,6')with b as (select ROW_NUMBER()over(order by id) rn from sysobjects )
select SUBSTRING(a.number,b.rn * 2 - 1 ,1)
from test1 a, b
where rn <= 6 -- 2. ----
create table test2(number int)
insert into test2 values (1),(2),(3),(4),(5),(7),(8),(10),(11),(13),(15)with b as (select number - ROW_NUMBER() over(order by number) rn, number from test2 )
select case when MIN(number) = MAX(number) then cast(MAX(number) as varchar(10))
else CAST(min(number) as varchar(10)) + '-' + CAST(max(number) as varchar(10))
end
from b
group by rn--3. ----
create table test4 (name varchar(10),mytype varchar(10),cj int )
insert into test4
values('张三','语文',83),('张三','数学',65),('张三','物理',85),
('李四','语文',73),('李四','数学',69),('李四','物理',93)select name ,
'语文' , max(case when mytype = '语文' then cj else 0 end),
'数学' , max(case when mytype = '数学' then cj else 0 end),
'物理' , max(case when mytype = '物理' then cj else 0 end)
from test4
group by name --4. ----
create function fn_times(@str1 varchar(10) , @str2 varchar(100))
returns int
as
begin
return LEN(@str2) - LEN(REPLACE( @str2 , @str1 , ''))
end
go
select dbo.fn_times('1','112345')
go--5. ----
declare @t table(
name varchar(30) ,
rows int ,
reserved varchar(40) ,
data varchar(40) ,
index_size varchar(40) ,
unused varchar(40)
)
declare @name varchar(60)
declare s cursor
for select name from sysobjects where xtype = 'u'
open s
fetch next from s into @name
while @@FETCH_STATUS = 0
begin
insert into @t exec sp_spaceused @name
fetch next from s into @name
end
close s
deallocate s
-- 按数据大小排序
select * from @t order by CONVERT(int, REPLACE(data,'KB','')) desc
--6. ----create proc p_nono(@plen int , @str varchar(100) output)
as
begin
declare @i int = 0 , @j int
declare @result varchar(100) = ''
while @i < @plen
begin
select @i = @i + 1
select @j = RAND() * 37 from (select 1 c )x
select @result = @result + char( 65 + @j * @i % 25 )
end
set @str = @result
end
godeclare @var varchar(100)
exec p_nono 10,@var output
print @var
第二题,itpub 在前两年是个出名的例子。
第三题,典型的行转列。
第四题,没什么说的。
第五题,从 sql 2000 开始就用的方法,那个时候用临时表,2005 后,可以用表变量了。
第六题,没什么说的。
-- 第六题,有两句写的太啰嗦了,改了一下
select @j = RAND() * 25 from (select 1 c )x
select @result = @result + char( 65 + @j )
那这个不是最大结果就是char(22.99+97) 肯定小于120,最后3个字母不是取不到吗?这个我也没算清楚,就是临时试出来的,可以全面综合考虑一下。我测试了下。取出来10000个。如果随机的字母是Z就跳出。发现的确是取不到Z。应该是*26 就可以。
只是看我计算机基础知识的掌握吧。
declare @str varchar(8000)
select @str=' select '+REPLACE(''+number+'',',',' number union all select ') from test1
exec(@str)
--------1
create table #test1 (number varchar(100))
insert into #test1 values ('1,2,3,4,5,6')
insert into #test1 values ('1,2,3,4,5,6')SELECT t.c.value('.','int')
FROM(
SELECT CONVERT(XML,'<x>'+replace(number,',','</x><x>')+'</x>') AS B
from #test1) a
CROSS APPLY a.B.nodes('/x') T(c) ------------2
create table #test2(number int)
insert into #test2(number) values
(1)insert into #test2(number) values (2)
insert into #test2(number) values (3)
insert into #test2(number) values (4)
insert into #test2(number) values (5)
insert into #test2(number) values (7)
insert into #test2(number) values (8)
insert into #test2(number) values (10)
insert into #test2(number) values (11)
insert into #test2(number) values (13)
insert into #test2(number) values (15);WITH cte AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS rn FROM #test2
)
,t AS
(
SELECT * ,1 AS partitionId FROM cte WHERE rn=1
UNION ALL
SELECT cte.*,CASE WHEN t.number+1=cte.number THEN t.partitionId
ELSE t.partitionId+1 END AS partitionId
FROM cte ,t
WHERE cte.rn=t.rn+1
)
SELECT CASE WHEN MIN(t.number)=MAX(t.number) THEN CAST(MAX(t.number)AS VARCHAR) ELSE
CAST(MIN(t.number)AS VARCHAR)+'~'+CAST(MAX(t.number)AS VARCHAR) END
FROM t
GROUP BY t.partitionId------------3
create table #test4 (name varchar(10),mytype varchar(10),cj int )
insert into #test4 values('张三','语文',83)
insert into #test4 values('张三','数学',65)
insert into #test4 values('张三','物理',85)
insert into #test4 values('李四','语文',73)
insert into #test4 values('李四','数学',69)
insert into #test4 values('李四','物理',93)SELECT name
,STUFF((SELECT ','+mytype FROM #test4 AS B WHERE A.name=B.name FOR XML PATH('')),1,1,'')
,STUFF((SELECT ','+CAST(cj AS VARCHAR) FROM #test4 AS B WHERE A.name=B.name FOR XML PATH('')),1,1,'')
FROM #test4 AS A
GROUP BY name--4,写一个函数,输入2个varchar参数@str1,@str2,返回字符串@str1 在@str2中出现的次数,@int.
--如(@str2=’abcsaac',@str1='a') 返回3.
DECLARE @strA VARCHAR(20)
DECLARE @strB VARCHAR(1)
SELECT @strA='abcsaac'
SELECT @strB='a'
SELECT (len(@strA) - len(replace(@strA,@strB,'')))/len(@strB)-----------5,查询所有表空间的大小。并按照从大到小排序。
SELECT '不知道,没写过'
----------6,写一个存储过程。输入参数@int,返回随机@int 个字母。如输入5个。返回随机5个字母。
SELECT '做一张字母常用表,随机数row_number() over(order by newid())去取就行了'
@str1 varchar(100),
@str2 varchar(100)
)
returns int
as
begin
declare @len int;
declare @n int;
if(len(@str1)>0 and len(@str2)>0)
begin
set @len=len(@str1);
set @n=0;
set @n=(len(@str2)-len(replace(@str2,@str1,'')))/@len;
end
return @n;
end
(
@n int
)
as
begin
declare @i int;
set @i=1;
while @i<=@n
begin
print char(cast(ceiling(rand() *25) as int)+64);
set @i=@i+1;
end
endexec printLetter 6
FOR SELECT number FROM test2--打开一个游标
OPEN ub
--循环一个游标
DECLARE @num int
declare @nfirst varchar(20)
declare @nend varchar(20)
set @nfirst=''
set @nend='';
declare @nlast int
set @nlast=0;
FETCH NEXT FROM ub INTO @num
WHILE @@FETCH_STATUS =0
BEGIN
if @nfirst=''
begin
set @nfirst=CAST(@num as varchar)
if @nlast=0 set @nlast=@num;
end
else
begin
if @num-@nlast=1
begin
set @nend='~'+CAST(@num as varchar)
set @nlast=@num
end
else
begin
print @nfirst+@nend;
set @nfirst=CAST(@num as varchar)
set @nend=''
set @nlast=@num
end
end
FETCH NEXT FROM ub INTO @num
END
print @nfirst+@nend;
--关闭游标
CLOSE ub
--释放资源
DEALLOCATE ub
工资只是工作的一个方面。我本人的确比较喜欢编程。当时大学啥都不懂。你只知道物流好的方面
我以前在德邦做物流,每天8点半上班。平均晚上9点下班。
根本没有加班这一说话。其实大半时间都在应付领导。各种检查。各种标准。
都是面子工程。呵呵,慢慢来,我觉得你现在最重要的就是要有一个机会,进入到这个行业里,然后有一个环境,这样学习效率才能更高,虽然工资看上去和这些题相比,有点低了,但是我想想我一开始的时候从一个每天用用excel的,到专业数据库开发,我的工资也就是5000,而且我都有好几年的工作经验了。所以,我觉得如果没有其他合适的机会,可以现在这个公司多练练
工资只是工作的一个方面。我本人的确比较喜欢编程。当时大学啥都不懂。你只知道物流好的方面
我以前在德邦做物流,每天8点半上班。平均晚上9点下班。
根本没有加班这一说话。其实大半时间都在应付领导。各种检查。各种标准。
都是面子工程。呵呵,慢慢来,我觉得你现在最重要的就是要有一个机会,进入到这个行业里,然后有一个环境,这样学习效率才能更高,虽然工资看上去和这些题相比,有点低了,但是我想想我一开始的时候从一个每天用用excel的,到专业数据库开发,我的工资也就是5000,而且我都有好几年的工作经验了。所以,我觉得如果没有其他合适的机会,可以现在这个公司多练练
谢谢 指点。我拒绝了。因为我现在是在职。工资虽然低了点。不过我基本没事情做。可以看看书。偶尔会有一点新业务开发。
经理对我很好的。只是去面试下 给自己学习找点方向。没有比较好的现在 基本不考虑离职。
--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)
;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)
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.串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 --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