解决方案 »
- 在一台机器上安装SQLServer和Oracle会不会用冲突阿?
- 请教一个SQL语句的写法,请大家帮帮忙,谢谢了,在线等待
- 从vfp把数据导入到sql2k里面
- --如何取得指定数据库的所有用户表的主键名称及主键字段名称?
- 保存图片的问题
- 如何看到进程的具体内容?
- SQL更新问题现有A表(InvID,Qty1,Qty2),B表(InvID,Qty),现在要更新表A.Qty2=A.Qty2+B.qty where A.Inv
- 如何在T-SQL中使用动态SQL?
- 樹形問題,請求怎樣解決??
- 触发器小问:如何将内欠过程的@@error带出?兼执行效率问题!
- 关于循环问题
- sqlserver如何获取日期中间点
就是要判断计算出来的年龄数在年限表里面那个范围
如果小于最大值大于或等于最小值,就获取得小值的Allowance里面的值
if(num>min(num))
begin
if(num<max(num))
begin
...
end
end
INSERT INTO #A(ID,SDATE)
VALUES(1,' 2001-02-13 09:06:15.287')
,(2,' 2003-02-13 09:06:15.287')
,(3,' 2006-02-13 09:06:15.287')select Allowance=
(case when datepart(yy,getdate())-datepart(yy,SDATE) between 0 and 0.99999999 then 10
when datepart(yy,getdate())-datepart(yy,SDATE) between 1 and 2.99999999 then 40
when datepart(yy,getdate())-datepart(yy,SDATE) between 3 and 4.99999999 then 60
else 0 end
), * from #a a
create table t1(ID int,进厂时间 datetime)
insert into t1
select 1,'2001-02-13 09:06:15.287' union all
select 2,'2003-03-13 09:06:15.287' union all
select 3,'2006-06-13 09:06:15.287' union all
select 4,'2010-06-13 09:06:15.287'
gocreate table t2(ID int,WorkingAge int,Allowance int)
insert into t2
select 1,1,10 union all
select 2,3,40 union all
select 3,5,60
go;with AcHerat as
(
select a.ID,a.进厂时间,b.WorkingAge,b.Allowance,datediff(yy,a.进厂时间,getdate()) yy,
rid=row_number() over (partition by a.ID order by b.WorkingAge)
from t1 a left join t2 b on datediff(yy,a.进厂时间,getdate()) >= b.WorkingAge
)select ID,进厂时间,WorkingAge,Allowance,yy
from AcHerat t
where rid = (select max(rid) from AcHerat where ID = t.ID)drop table t1,t2
/*************************ID 进厂时间 WorkingAge Allowance yy
----------- ----------------------- ----------- ----------- -----------
1 2001-02-13 09:06:15.287 5 60 10
2 2003-03-13 09:06:15.287 5 60 8
3 2006-06-13 09:06:15.287 5 60 5
4 2010-06-13 09:06:15.287 1 10 1(4 行受影响)
????????
INSERT INTO #A(ID,SDATE)
VALUES(1,' 2001-02-13 09:06:15.287')
,(2,' 2003-02-13 09:06:15.287')
,(3,' 2006-02-13 09:06:15.287')
,(4,' 2008-02-13 09:06:15.287')
select *,( select top 1 Allowance from #b where datepart(yy,getdate())-datepart(yy,SDATE)>=WorkingAge order by WorkingAge desc )
from #A
---------------------------------
ID SDATE (无列名)
1 2001-02-13 09:06:15.287 60
2 2003-02-13 09:06:15.287 60
3 2006-02-13 09:06:15.287 60
4 2008-02-13 09:06:15.287 40
忘记了这段代码
其实你还少了个条件,就是如果一直大于5年的,最多也只能拿60块钱,如果小于1年的就没有钱拿,是这个意思不?
select id,get_in_time
(select top 1 Allowance
from WorkingAge_tab
where WorkingAge>=(year(getdate())-year(get_in_time))
order by WorkingAge desc
) as Allowance
from get_in_tb
你的思路真的是好,再来排一次充用RID来判断.
insert into t1
select'001', 1,'2001-02-13 09:06:15.287' union all
select'002', 3,'2003-03-13 09:06:15.287' union all
select'003', 2,'2006-06-13 09:06:15.287' union all
select'004', 4,'2010-06-13 09:06:15.287'
gocreate table t2(ID int,WorkingAge int,PostID int,Allowance int)
insert into t2
select 1,1,2,10 union all
select 2,3,3,40 union all
select 3,5,3,60
go