两个表 员工表 工资税金对应表
emp taxinfo
=========== ============
empname maxsalval
empname minsalval
empsal taxlevel 交税等级 需要选出交税等级为一的员工的名字
emp taxinfo
=========== ============
empname maxsalval
empname minsalval
empsal taxlevel 交税等级 需要选出交税等级为一的员工的名字
from 员工表 a
where exists(select 1 from 工资税金对应表 where 等级 = '一' and a.交税等级 = 等级)
create table emp(empid int IDENTITY(1,1) PRIMARY KEY,empname varchar(10),empsal int)
insert into emp select 'A', '3231'
insert into emp select 'B', '1966'
insert into emp select 'C', '5564'
insert into emp select 'D', '3544'
insert into emp select 'E', '5123'
go--select * from empcreate table taxinfo(id int identity(1,1) primary key,minsalval int,maxsalval int,taxlevel varchar(4))
insert into taxinfo select '0', '2000','一'
insert into taxinfo select '2001', '3000','二'
insert into taxinfo select '3001', '4000','三'
insert into taxinfo select '4001', '5000','四'
insert into taxinfo select '5001', '10000','五'
go--select * from taxinfo--创建函数获取交税等级
create function f_getLvl(@val int)
returns varchar(1000)
as
begin
declare @lvl varchar(1000)
set @lvl=''
select @lvl = taxlevel from taxinfo where maxsalval>=@val and minsalval<=@val
return @lvl
end
go--使用临时表,选出交税等级为一的员工的名字
create table #tmp (tmpid int,tmpname varchar(10),tmplvl varchar(4))
insert into #tmp select empid,empname,dbo.f_getLvl(empsal) from emp
select * from #tmp where tmplvl='一'--删除测试数据
--drop table #tmp
--drop function f_getLvl
--drop table emp
--drop table taxinfo
FROM [emp] T0 INNER JOIN
[taxinfo] T1 ON T0.empsal > T1.minsalval AND T0.empsal <= T1.maxsalval AND T1.taxlevel = 1测试通过
select *
from emp
where empsal between (select minsalval from taxinfo where taxlevel='一' ) and (select maxsalval from taxinfo where taxlevel='一')运行结果:
empid empname empsal
----------- ---------- -----------
2 B 1966(1 row(s) affected)
select *
from emp
where empsal >= (select minsalval from taxinfo where taxlevel='一' ) and empsal<=(select maxsalval from taxinfo where taxlevel='一')
where empsal between minsalval and maxsalval and taxlevel='一'2 B 1966 1 0 2000 一我想了想感觉这个好像挺好的,我试了试能用。看来我面试的时候做对了,感谢 yhtapmys 给我做好了表。
当然我这个方法看着相当原始,大家都用了 inner join 要不就是临时表,我都不太会用啊,还要向大家学习啊
例如有些时候你一个select嵌着一个select,套了五六个,也许用一个临时表就能大大简化,这个时候就可以用临时表了,临时表速度也不慢。或者有时候你需要对用户导入的Excel中的数据进行复杂的处理,甚至要与SQL数据库中的信息关联,那你只好用临时表了。
——怎么忽然问起这个问题-_-