(下)今天去面试的题，我贴出来，大家看看
6:(Optional/可选的)
A:   Salary（薪水)
    EmpID   varchar(10)   not   null,
    DepartID   varchar(5)   not   null   foreign   key   references   Department(DepartID)  
    Salary   numeric(12)   null
B:   Department(部门)
    DepartID   varchar(5)
    DepartName   varchar(50)
C:   Employee(雇员)
    EmpID   varchar(10)   not   null   primary   key,
    EmpName   varchar(20)   not   null
写一个SQL语句，找出薪水为第三的雇员的名字

<上机题>
写一个存储过程，把一个输入的参数（类型varchar(4000))   的按每行40   个字符进行分解，并且不能把整个字分开，最后返回一个一个字段的结果集。
A:不考虑中文问题
B:不用考虑一个字的长度大于每行的最大长度问题

例:str(varchar(4000))   ->   "brand   are   among   the   most   trusted   names   in   business "
按每行10个字符，则分解结果为：
brand   are  
among   the
most
trusted
names   in
business


------解决方案--------------------
-- 上机题 ，简单的

Create proc proName
@str varchar(4000),
@len int
as
Declare @ss varchar(80), @n int
Create table #t (col varchar(80))
while len(@str)> 0
begin
Select @ss= ' ', @n=0
while (Len(@ss) <@len) and (len(@str)> 0)
begin
set @n=Charindex( ' ',@Str)
if @n> 0
begin
if Len(@ss+Left(@Str,@n)) <=@len
Select @ss=@ss+Left(@Str,@n),@Str=Stuff(@Str,1,@n, ' ')
else break
end
else
if len(@ss+@Str) <=@len
Select @ss=@ss+@Str,@Str= ' '
else break
end
Insert into #t Select @ss
end
Select * from #t
go

exec proName 'a d brand are among the most trusted names in business ',10
------解决方案--------------------
--6

select * from Employee
inner join
(
select EmpID from Salary as tmp
where (select count(*) from Salary where Salary> tmp.Salary)=3
)Salary on Employee.EmpID=Salary.EmpID

------解决方案--------------------
还早嘛
------解决方案--------------------
学习
------解决方案--------------------
学习
------解决方案--------------------
declare @str varchar(4000)
declare @len int
declare @T table (T varchar(80))
declare @i int
set @i=1
while ((@i-1)+@len) <=len(@str)
begin
insert @T select SUBSTRING(@str,@i,@len)
set @i=@i+@len
end
if(len(@str)%@len)> 0
insert @T select right(@str,len(@str)-(len(@str)/@len)*@len)
------解决方案--------------------
我暈...全是夜貓子,^^
------解决方案--------------------
高手,隐藏的够深的哈
------解决方案--------------------
--算工資的那個..maybe需要考慮有相同工資的,
select EmpName from Employee A
inner join
(
select EmpID from Salary t
where (select count(distinct Salary) from Salary where Salary> =t.Salary)=3
) B
on A.EmpID=B.EmpID