/*
2.d类型的计算,根据d以后的code号码汇总所有qumoshu的数据,例如d2,3,5,6,7,8是code为2,3,5,6,7行
的qumoshu相加.本人写的语句如下:
update set qumoshu=(select sum(a.qumoshu)
from t_fn_zhechangfuzhaibiao as a where a.code in
(substring(t_fn_zhechangfuzhaibiao.gongsi,2,len(t_fn_zhechangfuzhaibiao.gongsi)-1)))
where left(gongsi,1)='d'
*/UPDATE n
SET n.qumoshu=
(SELECT SUM(x.qumoshu)
FROM t_fn_zhechangfuzhaibiao x
WHERE charindex(','+CONVERT(CHAR,(x.code))+',', ','+n.gongsi+',')>0)
FROM t_fn_zhechangfuzhaibiao n
WHERE Left(n.gongsi,1)='d'
2.d类型的计算,根据d以后的code号码汇总所有qumoshu的数据,例如d2,3,5,6,7,8是code为2,3,5,6,7行
的qumoshu相加.本人写的语句如下:
update set qumoshu=(select sum(a.qumoshu)
from t_fn_zhechangfuzhaibiao as a where a.code in
(substring(t_fn_zhechangfuzhaibiao.gongsi,2,len(t_fn_zhechangfuzhaibiao.gongsi)-1)))
where left(gongsi,1)='d'
*/UPDATE n
SET n.qumoshu=
(SELECT SUM(x.qumoshu)
FROM t_fn_zhechangfuzhaibiao x
WHERE charindex(','+CONVERT(CHAR,(x.code))+',', ','+n.gongsi+',')>0)
FROM t_fn_zhechangfuzhaibiao n
WHERE Left(n.gongsi,1)='d'
UPDATE n
SET n.qumoshu=
(SELECT SUM(x.qumoshu)
FROM t_fn_zhechangfuzhaibiao x
WHERE charindex(','+CONVERT(CHAR,(x.code))+',', ','+n.gongsi+',')>0)
FROM t_fn_zhechangfuzhaibiao n
WHERE Left(n.gongsi,1)='d'
UPDATE n
SET n.qumoshu=
(SELECT SUM(case when charindex(','+CONVERT(CHAR,(x.code))+',', ','+right(n.gongsi,len(n.gongsi)-1)+',')>0 then x.qumoshu when charindex(',-'+CONVERT(CHAR,(x.code))+',', ','+right(n.gongsi,len(n.gongsi)-1)+',')>0 then -x.qumoshu else 0 end)
FROM t_fn_zhechangfuzhaibiao x)
FROM t_fn_zhechangfuzhaibiao n
WHERE Left(n.gongsi,1)='d'
在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用,那么该外部引用就必须是该表达式中所引用的唯一的一列。
SET n.qumoshu=
(SELECT SUM(x.qumoshu)
FROM t_fn_zhechangfuzhaibiao x
WHERE charindex(','+CONVERT(CHAR,(x.code))+',', ','+right(n.gongsi,len(n.gongsi)-1)+',')>0)-(SELECT SUM(x.qumoshu)
FROM t_fn_zhechangfuzhaibiao x
WHERE charindex(',-'+CONVERT(CHAR,(x.code))+',', ','+right(n.gongsi,len(n.gongsi)-1)+',')>0)
FROM t_fn_zhechangfuzhaibiao n
WHERE Left(n.gongsi,1)='d'