我在選擇陳述句下面有這個。在那里，我有 2 個派生列 EmpContbtnWithoutTax 和 EmpContbtnWithTax 我想將這些資料永久保存在同一個表中 EmpPFContributionTest
需要幫助讓 EmpPFContributionTest 表的更新查詢永久更新 EmpContbtnWithoutTax 和 EmpContbtnWithTax 列的相應行。更新我在下面首先提到的 1 列中嘗試的腳本
update t2
set EmpContbtnWithoutTax =
(
select case when sum(isnull(t1.emp_contribution,0)) over(partition by
t1.emp_no order by (t1.pcm_year * 100 t1.pcm_month))
sum(isnull(vpf,0)) over(partition by t1.emp_no order by (t1.pcm_year *
100 t1.pcm_month)) < 3000
then sum(isnull(t1.emp_contribution,0)) over(partition by
t1.emp_no order by (t1.pcm_year * 100 t1.pcm_month))
sum(isnull(vpf,0)) over(partition by t1.emp_no order by (t1.pcm_year *
100 t1.pcm_month))
else null
end
from EmpPFContributionTest t1
)
from EmpPFContributionTest t2
/*Actual Select Statement */
select case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month)) sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month)) < 3000
then sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month)) sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month))
else null
end
empcontbtnwithouttax,
case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month)) sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month)) >= 3000
then sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month)) sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 pcm_month))
else null
end
empcontbtnwithtax,
* from [dbo].EmpPFContributionTest
where
(pcm_year * 100 pcm_month >= 201504) AND
(pcm_year * 100 pcm_month < 201604)
and emp_no= 11101201
order by (pcm_year * 100 pcm_month)
uj5u.com熱心網友回復：
看起來您正在嘗試更新同一個表中的兩個現有列？
使用從現有資料計算資料的地方不是一個好的模式 - 當資料更改時，您的計算值立即無效。
話雖如此，要執行您的要求，您只需使用可更新的 CTE，例如：
with tax as (
select case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 pcm_month) sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 pcm_month) < 3000
then sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 pcm_month) sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 pcm_month)
else null
end as withouttax,
case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 pcm_month) sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 pcm_month) >= 3000
then sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 pcm_month) sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 pcm_month)
else null
end as withtax
from dbo.EmpPFContributionTest
where
pcm_year * 100 pcm_month >= 201504
and pcm_year * 100 pcm_month < 201604
and emp_no = 11101201
)
update tax set
EmpContbtnWithoutTax = withouttax
empcontbtnwithtax = withtax
uj5u.com熱心網友回復：
我已經完成了視圖
CREATE OR ALTER function fn_GetEmpPFContribution
(
@year nvarchar(4)
)
returns table
as
return(
select case when sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100 pfc.pcm_month)) sum(isnull(vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100 pfc.pcm_month)) < 3000
then sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100 pfc.pcm_month)) sum(isnull(vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100 pfc.pcm_month))
else null
end
[EmpContbtnWithoutTax],
case when sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pcm_year * 100 pfc.pcm_month)) sum(isnull(pfc.vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100 pfc.pcm_month)) >= 3000
then sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pcm_year * 100 pfc.pcm_month)) sum(isnull(pfc.vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100 pfc.pcm_month))
else null
end
[EmpContbtnWithTax],
pfc.*
from [dbo].[pf_contribution_master] pfc
where
(pfc.pcm_year * 100 pfc.pcm_month >= concat(@year,'04'))
AND
(pfc.pcm_year * 100 pfc.pcm_month < concat(@year 1,'04'))
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/389649.html
標籤：sql sql-server 查询语句