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
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
@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
goexec proName 'a d brand are among the most trusted names in business',10
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 @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)
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
--倒的
declare @char varchar(1000)
set @char = 'brand are among the most trusted names in business1'
declare @int dec(18,1)
set @int = len(@char)/1.0/10
while @int>0
begin
print substring(@char,cast(@int as int)*10,10)
set @int = @int - 1
end
--顺的
declare @char varchar(1000)
set @char = 'brand are among the most trusted names in business1'
declare @int dec(18,1),@sho int
set @int = len(@char)/1.0/10
set @sho = 0
while @int>@sho
begin
print substring(@char,cast(@sho as int)*10,10)
set @sho = @sho + 1
end
select EmpName from Employee A
inner join
(
select top 1 EmpID from ( select top 3 * from Salary order by Salary desc)
) B
on A.EmpID=B.EmpID
select EmpName from Employee A
inner join
(
select top 1 EmpID from ( select top 3 * from Salary order by Salary desc) a order by salary
) B
on A.EmpID=B.EmpID
select EmpName from Employee A
inner join
(
select top 1 EmpID from ( select top 3 * from Salary order by Salary) a order by salary desc
) B
on A.EmpID=B.EmpID
这样才行吧
@Input varchar(4000)
AS
DECLARE
@Index int,
@Result varchar(20)
SET @Result=''
CREATE TABLE #Temp(Result varchar(20) )
WHILE 1=1
BEGIN
SET @Index=CHARINDEX(' ',@Input)
IF (@Index=0)
BEGIN
INSERT INTO #Temp SELECT @Result UNION ALL SELECT @Input
BREAK
END
ELSE
BEGIN
IF LEN(@Result+LEFT(@Input,@Index))>10
BEGIN
INSERT INTO #Temp SELECT @Result
SET @Result=''
END
SET @Result=@Result+LEFT(@Input,@Index)
SET @Input=RIGHT(@Input,LEN(@Input)-@Index)
END
END
SELECT * FROM #Temp
DROP TABLE #Temp
GO
--调用例子:EXECUTE ProcName N'brand are among the most trusted names in business',10
CREATE PROCEDURE ProcName
@Input varchar(4000),@Len int
AS
DECLARE
@Index int,
@Result varchar(20)
SET @Result=''
CREATE TABLE #Temp(Result varchar(20) )
WHILE 1=1
BEGIN
SET @Index=CHARINDEX(' ',@Input)
IF (@Index=0)
BEGIN
INSERT INTO #Temp SELECT @Result UNION ALL SELECT @Input
BREAK
END
ELSE
BEGIN
IF LEN(@Result+LEFT(@Input,@Index))>@Len
BEGIN
INSERT INTO #Temp SELECT @Result
SET @Result=''
END
SET @Result=@Result+LEFT(@Input,@Index)
SET @Input=RIGHT(@Input,LEN(@Input)-@Index)
END
END
SELECT * FROM #Temp
DROP TABLE #Temp
GO
FROM salary
WHERE (empid IN
(SELECT TOP 3 empid
FROM salary
WHERE (money NOT IN
(SELECT MAX(salary)
FROM salary))
order by test_ desc
)) AND (empid NOT IN
(SELECT TOP 1 empid
FROM salary
WHERE (test_ NOT IN
(SELECT MAX(salary)
FROM salary))
order by salary desc ))ORDER BY salary DESC
我对需求理解是,第一名的假如500元有五人,第二名四百元有3人,第三名有六人,那就把这六个人姓名都选出来。不知道我写得sql对不对,本人接触数据库时间不长
w75251455(砍破)和WangZWang(先来) 算法都不行,
把整字分开了
---只有 DVD_01(OK_008) 的方法可以
我写了一个倒是可以查出工资相同的人,但工资排第三的人只有一个时会显示出多余的记录,与sxlcom(木头)的方法正好相反。哎,先一下,改天再考虑create table Department --(部门)
(
DepartID varchar(5) primary key not null,
DepartName varchar(50)
)create table Salary --(薪水)
(
EmpID varchar(10) not null,
DepartID varchar(5) not null foreign key references Department(DepartID),
Salary numeric(12) null
)create table Employee --(雇员)
(
EmpID varchar(10) not null primary key,
EmpName varchar(20) not null
)
goinsert into Employee values(1,'aaa');
insert into Employee values(2,'bbb');
insert into Employee values(3,'ccc');
insert into Employee values(4,'ddd');
insert into Employee values(5,'eee');
insert into Employee values(6,'fff');
insert into Employee values(7,'ggg');
insert into Employee values(8,'hhh');
--insert into Employee values(9,'iii');insert into Department values(1,'test');insert into Salary values(1,1,1000);
insert into Salary values(2,1,2000);
insert into Salary values(3,1,3000);
insert into Salary values(4,1,4000);
insert into Salary values(5,1,2500);
insert into Salary values(6,1,2200);
insert into Salary values(7,1,2200);
insert into Salary values(8,1,2200);
--insert into Salary values(9,1,1900);
goselect EmpName from Employee,Salary where Employee.EmpID = Salary.EmpID and
Salary.Salary in (select top 3 Salary from Salary where Salary not in
(select top 2 Salary from Salary order by Salary asc)order by Salary asc)
order by Salary asc;
select EmpName from employee e
join salary s
on e.EmpId=s.EmpId
where
s.salary=
(select top 1 salary from
(select DISTINCT top 3 salary from salary a
order by salary desc) b order by salary )
select EmpName from Employee emp,Salary sal
where sal.Salary = (
select top 1 tempS.Salary from Salary tempS
inner join
(
select DISTINCT top 3 a.Salary from Salary a order by a.Salary desc
) tempSE
on tempS.Salary = tempSE.Salary
order by tempS.Salary asc
) and emp.EmpID = sal.EmpID
我的Blog中有详细说明:http://farseek.spaces.live.com/blog/cns!8569EBE9EE780F12!225.entry#trackback
on
(employee.empid = salary.empid) wheresalary = (select * from (select top 3 salary from salary group by (salary)
order by salary desc)epmloyee
where salary not in (select top 2 salary from salary group by (salary)
order by salary desc)
)
这是参照wag1_1() 的提示写的,我简单测试了一下可以去掉重复工资:select empName ,salary from employee inner join salary on
(employee.empid = salary.empid) where salary =
(select * from (select top 3 salary from salary
group by (salary) order by salary desc)epmloyee where salary not in
(select top 2 salary from salary group by (salary) order by salary desc))倒数三行,查找到第三的工资,根据这个再查找雇员的名字,加了一个联结,顺便把工资打出来下面是建表的SQL语句,一并加上来:
--面试6题
create table Salary
(
EmpID varchar(10) not null foreign key references Employee(EmpID),
DepartID varchar(5) not null foreign key references Department(DepartID),
Salary numeric(10) null
)create table Department
(
DepartId varchar(5) primary key ,
DepartName varchar(20)
)create table Employee
(
EmpID varchar(10) primary key,
EmpName varchar(20) not null
)
insert into Department values('1','销售')
insert into Department values('2','财务')
insert into Department values('3','技术')insert into Employee values('1','张三1')
insert into Employee values('2','李四2')
insert into Employee values('3','王五3')
insert into Employee values('4','张三2')
insert into Employee values('5','李四2')
insert into Employee values('6','王五2')
insert into Employee values('7','张三3')
insert into Employee values('8','李四4')
insert into Employee values('9','王五5')insert into Salary values('1','1',1000)
insert into Salary values('2','1',1200 )
insert into Salary values('3','1',1250 )
insert into Salary values('4','2',2000 )
insert into Salary values('5','2',1300 )
insert into Salary values('6','2',1250)
insert into Salary values('7','3',1000 )
insert into Salary values('8','3',2200 )
insert into Salary values('9','3',2000 )--写一个SQL语句,找出薪水为第三的雇员的名字