--> 测试数据: @TB
declare @TB table (TBRSF int,DWLB varchar(1),DWDM varchar(3),MYD int,ZHMYD sql_variant)
insert into @TB
select 1,'A','A01',80,null union all
select 2,'A','A01',70,null union all
select 3,'A','A01',90,null union all
select 4,'A','A01',40,null union all
select 0,'A','A01',60,null union all ---> 更新 tbrsf=0 的记录 的zhmyd 字段select 1,'A','A02',90,null union all
select 2,'A','A02',50,null union all
select 3,'A','A02',55,null union all
select 4,'A','A02',95,null union all
select 0,'A','A02',60,null/*
要求:更新 tbrsf=0 的记录 的zhmyd 字段
如 A01: 80*0.1 (tbrsf=1 乘以0.1,2为0.2,3为0.3,4为0.4 )这个写成固定的就可以
+70*0.2
+90*0.3
+40*0.4 =65结果如下:TBRSF DWLB DWDM MYD ZHMYD
----------- ---- ---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A A01 80 NULL
2 A A01 70 NULL
3 A A01 90 NULL
4 A A01 40 NULL
0 A A01 60 651 A A02 90 NULL
2 A A02 50 NULL
3 A A02 55 NULL
4 A A02 95 NULL
0 A A02 60 73.5(10 行受影响)
*/
ZHMYD = (SELECT SUM(MYD*TBRSF/10.)
FROM @TB
WHERE DWLB=A.DWLB AND DWDM=A.DWDM
AND TBRSF <> 0)
FROM @TB AS A
WHERE TBRSF = 0;
UPDATE A
SET ZHMYD=B.ZHMYD
FROM @TB A,
(
SELECT DWDM,ZHMYD=SUM(CASE WHEN TBRSF=1 THEN MYD*0.1
WHEN TBRSF=2 THEN MYD*0.2
WHEN TBRSF=3 THEN MYD*0.3
WHEN TBRSF=4 THEN MYD*0.4 END)
FROM @TB
WHERE TBRSF=0
GROUP BY DWDM
)B
WHERE TBRSF=0 AND A.DWDM=B.DWDM
--怎么这样不对的呢
set zhmyd = x
from @tb a
inner join
(
select SUM(tbrsf*0.1*myd) x,dwdm from @tb group by dwdm
) b
on a.dwdm=b.dwdm and a.tbrsf=0
FROM @TB AS T1,(SELECT DWDM,SUM(MYD*TBRSF*0.1 ) AS TOTAL FROM @TB WHERE TBRSF<>0 GROUP BY DWDM ) AS T2
WHERE T1.DWDM=T2.DWDM AND T1.TBRSF=0SELECT * FROM @TB
insert into @TB
select 1,'A','A01',80,null union all
select 2,'A','A01',70,null union all
select 3,'A','A01',90,null union all
select 4,'A','A01',40,null union all
select 0,'A','A01',60,null union all ---> 更新 tbrsf=0 的记录 的zhmyd 字段select 1,'A','A02',90,null union all
select 2,'A','A02',50,null union all
select 3,'A','A02',55,null union all
select 4,'A','A02',95,null union all
select 0,'A','A02',60,null
--UPDATE @TB SET ZHMYD=UPDATE T SET ZHMYD=B.ZHMYD FROM @TB T,
(
SELECT DWLB,DWDM, SUM(MYD*CONVERT(DEC(18,1),('0.'+LTRIM(TBRSF)))) AS ZHMYDFROM @TB GROUP BY DWLB,DWDM)B WHERE T.TBRSF=0 AND T.DWLB=B.DWLB AND T.DWDM=B.DWDMSELECT * FROM @TB
TBRSF DWLB DWDM MYD ZHMYD
----------- ---- ---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A A01 80 NULL
2 A A01 70 NULL
3 A A01 90 NULL
4 A A01 40 NULL
0 A A01 60 65.0
1 A A02 90 NULL
2 A A02 50 NULL
3 A A02 55 NULL
4 A A02 95 NULL
0 A A02 60 73.5(所影响的行数为 10 行)
1 0.25
2 0.1
3 0.2
4 0.45 大家帮看看
ZHMYD = (SELECT SUM(MYD*CASE WHEN TBRSF=1 THEN 0.25
WHEN TBRSF=2 THEN 0.1
WHEN TBRSF=3 THEN 0.2
WHEN TBRSF=4 THEN 0.45 END)
FROM @TB
WHERE DWLB=A.DWLB AND DWDM=A.DWDM
AND TBRSF <> 0)
FROM @TB AS A
WHERE TBRSF = 0;
WHEN TBRSF=2 THEN MYD*0.2
WHEN TBRSF=3 THEN MYD*0.3
WHEN TBRSF=4 THEN MYD*0.4 END)
FROM @TB
WHERE TBRSF=0 --这个不要就行了
GROUP BY DWDM
insert into @TB
select 1,'A','A01',80,null union all
select 2,'A','A01',70,null union all
select 3,'A','A01',90,null union all
select 4,'A','A01',40,null union all
select 0,'A','A01',60,null union all ---> 更新 tbrsf=0 的记录 的zhmyd 字段
select 1,'A','A02',90,null union all
select 2,'A','A02',50,null union all
select 3,'A','A02',55,null union all
select 4,'A','A02',95,null union all
select 0,'A','A02',60,null
declare @TB1 table (TBRSF int,FLOAT1 DEC(18,2))insert into @TB1
select 1,0.25union all
select 2,0.1 union all
select 3,0.2 union all
select 4,0.45
UPDATE T SET ZHMYD=B.ZHMYD FROM @TB T,
(
SELECT
DWLB,DWDM, SUM(MYD*FLOAT1) AS ZHMYDFROM
@TB TT,@TB1 TT1
WHERE TT.TBRSF=TT1.TBRSF AND TT.TBRSF<>0
GROUP BY DWLB,DWDM)B WHERE T.TBRSF=0 AND T.DWLB=B.DWLB AND T.DWDM=B.DWDMSELECT * FROM @TB
(所影响的行数为 10 行)
(所影响的行数为 4 行)
(所影响的行数为 2 行)TBRSF DWLB DWDM MYD ZHMYD
----------- ---- ---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A A01 80 NULL
2 A A01 70 NULL
3 A A01 90 NULL
4 A A01 40 NULL
0 A A01 60 63.00
1 A A02 90 NULL
2 A A02 50 NULL
3 A A02 55 NULL
4 A A02 95 NULL
0 A A02 60 81.25(所影响的行数为 10 行)
FROM @TB
WHERE TBRSF=0
GROUP BY DWDM