如奖罚情况表(JFQK)中的字段如下
lib reday ysnopay ybnopay jbnopay nopay
永塑 2002-1-1 1000
永塑 2002-2-1 2000
永包 2002-1-1 2030
经营部 2002-1-1 2050
总部 2002-1-1 5080
请问如何把日期(reday)相同的记录作如下处理:
若lib为永塑则让ysnopay=nopay (如第一条记录ysnopay=1000)
若lib为永包则让ybnopay=nopay (如第三条记录ybnopay=2030)
若lib为经营部则让jbnopay=nopay (如第四条记录jbnopay=2050)
若lib为总部则把前面日期相同的lib为永塑、永包、经营部的nopay值分别赋于lib为总部的ysnopay,ybnopay,jbnopay (如最后一条记录ysnopay=1000,ybnopay=2030,jbnopay=2050)望各位高手指点,解决即给分
lib reday ysnopay ybnopay jbnopay nopay
永塑 2002-1-1 1000
永塑 2002-2-1 2000
永包 2002-1-1 2030
经营部 2002-1-1 2050
总部 2002-1-1 5080
请问如何把日期(reday)相同的记录作如下处理:
若lib为永塑则让ysnopay=nopay (如第一条记录ysnopay=1000)
若lib为永包则让ybnopay=nopay (如第三条记录ybnopay=2030)
若lib为经营部则让jbnopay=nopay (如第四条记录jbnopay=2050)
若lib为总部则把前面日期相同的lib为永塑、永包、经营部的nopay值分别赋于lib为总部的ysnopay,ybnopay,jbnopay (如最后一条记录ysnopay=1000,ybnopay=2030,jbnopay=2050)望各位高手指点,解决即给分
@strdate DATETIME
as
select * from jfqk where reday=@strdate
go
set ysnopay=J.nopay
From JFQK as J
Where lib='永塑'
////////////
同样的有
Update JFQK
set ybnopay=J.nopay
From JFQK as J
Where lib='永包'
//////////////
Update JFQK
set jbnopay=J.nopay
From JFQK as J
Where lib='经营部'
////////////////
Update JFQK
set ysnopay=J1.nopay,ybnopay=J2.nopay,jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3
Where lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
reday=J1.reday and reday=J2.reday and reday=J3.reday
刚写的,你试试
set ysnopay=J1.nopay,ybnopay=J2.nopay,jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3
Where lib in (select distinct lib from JFQK)
这样的话你的lib就是活的了
set ysnopay=J1.nopay,ybnopay=J2.nopay,jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3
Where JFQK.lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
JFQK.reday=J1.reday and JFQK.reday=J2.reday and JFQK.reday=J3.reday
//////////////////////////
再试试这个
set J4.ysnopay=J1.nopay,ybnopay=J2.nopay,jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3
Where J4.lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
J4.reday=J1.reday and J4.reday=J2.reday and J4.reday=J3.reday
//////////////////////////
再试试这个
set JFQK.ysnopay=J1.nopay,ybnopay=J2.nopay,jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3
Where JFQK.lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
JFQK.reday=J1.reday and JFQK.reday=J2.reday and JFQK.reday=J3.reday
//////////////////////////
或者是这样,反正是要把四个JFQK分清楚,我这边不好试,你再改的试一试,
一个说AS附近有语法错误,一个还是JFQK不明确
set J4.ysnopay=J1.nopay,ybnopay=J2.nopay,jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3,JFQK as J4
Where J4.lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
J4.reday=J1.reday and J4.reday=J2.reday and J4.reday=J3.reday
Update JFQK
set JFQK.ysnopay=J1.nopay,JFQK.ybnopay=J2.nopay,JFQK.jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3
Where JFQK.lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
JFQK.reday=J1.reday and JFQK.reday=J2.reday and JFQK.reday=J3.reday
////////////////////////////////////////
Update JFQK
set J4.ysnopay=J1.nopay,J4.ybnopay=J2.nopay,J4.jbnopay=J3.nopay
From JFQK as J1,JFQK as J2,JFQK as J3,JFQK as J4
Where J4.lib='总部' and J1.lib='永塑' and J2.lib='永包' and J3.lib='经营部' and
J4.reday=J1.reday and J4.reday=J2.reday and J4.reday=J3.reday
UPDATE jfqk
SET JFQK.ysnopay = J1.nopay, ybnopay = J2.nopay,jbnopay=J3.nopay
FROM JFQK, JFQK AS J1, JFQK AS J2
WHERE JFQK.lib = '总部' AND J1.lib = '永塑' AND J2.lib = '永包'and J3.lib='经营部' AND
JFQK.reday = J1.reday AND JFQK.reday = J2.reday AND JFQK.reday = J3.reday and
JFQK.company = J1.company AND JFQK.company = J2.company and JFQK.company = J3.company不过我发现这样写有一点不好,不好意思上面是我自己没说清楚,还有一个公司字段(company),总部汇总的公司都在永塑,永包和经营部内,即总部ysnopay,ybnopay,jbnopay中至少有一个有数据,并不是必须三个都有数据,而上面的语句只能使三个分部都有这家公司时才对总部写数据而我想对总部的每家公司的ysnopay,ybnopay,jbnopay都有想应的值,在您的指导下我知道了对表UPDATE三次可以达到这个目的,能不能UPDATE一次实现这样的效果呢?
UPDATE JFQK
SET JFQK.ysnopay = J1.nopay
FROM JFQK as J1
WHERE JFQK.lib = '总部' AND J1.lib = '永塑' AND JFQK.reday = J1.reday AND
JFQK.company = J1.company
////////////////////////////////
UPDATE JFQK
SET JFQK.ybnopay = J1.nopay
FROM JFQK as J1
WHERE JFQK.lib = '总部' AND J1.lib = '永包' AND JFQK.reday = J1.reday AND
JFQK.company = J1.company
////////////////////////////////
UPDATE JFQK
SET JFQK.jbnopay = J1.nopay
FROM JFQK as J1
WHERE JFQK.lib = '总部' AND J1.lib = '经营部' AND JFQK.reday = J1.reday AND
JFQK.company = J1.company
////////////////////////////////////////
不知道我理解对你的意思没有?