请问“子查询引用外层查询的列”该怎么写？

TIGER0579 2019-07-18 05:59:47

if object_id('tempdb.dbo.#k') is not null drop table #k
CREATE TABLE #k (code varchar(6),sort int,t1 decimal(18,6),t2 decimal(18,6),t3 decimal(18,6))
update #k set T3=(select sum(t1-t2) from #k b where b.code=#k.code and b.sort<=#k.sort and b.sort>#k.sort-10)
上面的语句执行没问题,但执行下列语句会报错:在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用，那么该外部引用就必须是该表达式中所引用的唯一的一列。

update #k set T3=(select sum(t1-#k.t2) from #k b where b.code=#k.code and b.sort<=#k.sort and b.sort>#k.sort-10)

请问在子查询引用外层查询的列该怎么写？

...全文

856 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

limarton 2019-07-20

打赏
举报

仔细考虑了一下，你这个语句确实有问题，因为在这个子查询select sum(b.t1-a.t2) from #k b where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10 中，b选出的的行可能有多行，而a.t2只有一行，多行对一行，是无法进行对应的相减的，所以应该改成这样: update a set T3=( select sum(b.t1-c.t2) from #k b left join #k c on c.code=a.code and c.sort=a.sort --这两个字段必须能形成联合主键,否则,需要其它能做为主键的条件 b.主键=c.主键 where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10 ) from #k a

limarton 2019-07-20

打赏
举报

仔细考虑了一下，你这个语句确实有问题，因为在这个子查询select sum(b.t1-a.t2)
from #k b
where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10
中，b选出的的行可能有多行，而a.t2只有一行，多行对一行，是无法进行对应的相减的，所以应该改成这样:

update a
set T3=(
select sum(b.t1-c.t2) from #k b left join #k c on
b.code=a.code and b.sort=a.sort --这两个字段必须能形成联合主键,否则,需要其它能做为主键的条件 b.主键=c.主键
where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10
)
from #k a

唐诗三百首 2019-07-19

打赏
举报


  update a
   set a.T3=(select sum(b.t1)-a.t2
             from #k b 
             where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10)
   from #k a

RINK_1 2019-07-19

打赏
举报

引用 4 楼 RINK_1 的回复:

[quote=引用 3 楼 TIGER0579 的回复:] 上面2种都不行,会报错. update a set a.T3=(select sum(b.t1-a.t2) from #k b where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10) from #k a 报错: 在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用，那么该外部引用就必须是该表达式中所引用的唯一的一列。 UPDATE #K SET T3=A.TOTAL FROM (SELECT SUM(A.T1-B.T2) AS TOTAL FROM #K A OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B GROUP BY A.CODE) AS A JOIN #K B ON A.CODE=B.CODE 报错: 列名 'CODE' 无效。


SELECT SUM(A.T1-B.T2) AS TOTAL,A.CODE
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE

UPDATE #K
SET T3=A.TOTAL
FROM
(SELECT SUM(A.T1-B.T2) AS TOTAL,A.CODE
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE) AS A
JOIN #K B ON A.CODE=B.CODE

[/quote] 如果CODE+SORT相当于联合主键，就再试试下面的。



SELECT A.*,B.*
FROM #K A 
JOIN
(SELECT A.code,A.sort,SUM(B.t1-A.T2) AS TOTAL FROM #K A
OUTER APPLY (SELECT T1 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.code,A.sort) AS B ON A.code=B.code AND A.sort=B.sort

UPDATE #k
SET t3=B.TOTAL
FROM #K A 
JOIN
(SELECT A.code,A.sort,SUM(B.t1-A.T2) AS TOTAL FROM #K A
OUTER APPLY (SELECT T1 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.code,A.sort) AS B ON A.code=B.code AND A.sort=B.sort

唐诗三百首 2019-07-19

打赏
举报


update a
 set a.T3=c.t3
 from #k a
 cross apply(select t3=sum(b.t1)-a.t2*(select count(1) 
                                       from #k b 
                                       where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10)
             from #k b 
             where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10) c


select * from #k

/*
code   sort        t1                                      t2                                      t3
------ ----------- --------------------------------------- --------------------------------------- ---------------------------------------
a      1           101.000000                              99.000000                               2.000000
a      2           102.000000                              98.000000                               7.000000
a      3           103.000000                              97.000000                               15.000000
a      4           104.000000                              96.000000                               26.000000
a      5           105.000000                              95.000000                               40.000000
a      6           106.000000                              94.000000                               57.000000
a      7           107.000000                              93.000000                               77.000000
a      8           108.000000                              92.000000                               100.000000
a      9           109.000000                              91.000000                               126.000000
a      10          110.000000                              90.000000                               155.000000

(10 行受影响)
*/

TIGER0579 2019-07-19

打赏
举报

上面2种算法结果不对, 我想要的是求和(前10条的t1-本条的t2), 而不是前10条先求和,再减本条的t2, 附上用游标的算法, 哪位高人帮我改成子查询的算法, 谢谢！
if object_id('tempdb.dbo.#k') is not null drop table #k
CREATE TABLE #k (code varchar(6),sort int,t1 decimal(18,6),t2 decimal(18,6),t3 decimal(18,6))
--生成测试数据
declare @i int set @i =1 while @i <=10 begin insert #k(code,sort,t1,t2) select 'a',@i,100+@i,100-@i set @i = @i +1 end
-- 对于code只有一个的情况下,用游标的算法
declare @t2 decimal(18,6),@sort int
declare au cursor for select sort,t2 from #k order by sort
open au
FETCH NEXT FROM au INTO @sort,@t2
WHILE @@FETCH_STATUS = 0 BEGIN
update #k set t3=(select sum(t1-@t2) from #k where sort<=@sort and sort>@sort-10) where sort=@sort
FETCH NEXT FROM au INTO @sort,@t2
END
CLOSE au DEALLOCATE au
select * from #k

RINK_1 2019-07-18

打赏
举报

引用 3 楼 TIGER0579 的回复:

上面2种都不行,会报错. update a set a.T3=(select sum(b.t1-a.t2) from #k b where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10) from #k a 报错: 在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用，那么该外部引用就必须是该表达式中所引用的唯一的一列。 UPDATE #K SET T3=A.TOTAL FROM (SELECT SUM(A.T1-B.T2) AS TOTAL FROM #K A OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B GROUP BY A.CODE) AS A JOIN #K B ON A.CODE=B.CODE 报错: 列名 'CODE' 无效。


SELECT SUM(A.T1-B.T2) AS TOTAL,A.CODE
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE

UPDATE #K
SET T3=A.TOTAL
FROM
(SELECT SUM(A.T1-B.T2) AS TOTAL,A.CODE
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE) AS A
JOIN #K B ON A.CODE=B.CODE

TIGER0579 2019-07-18

打赏
举报

上面2种都不行,会报错.
update a
set a.T3=(select sum(b.t1-a.t2)
from #k b
where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10)
from #k a
报错: 在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用，那么该外部引用就必须是该表达式中所引用的唯一的一列。

UPDATE #K
SET T3=A.TOTAL
FROM
(SELECT SUM(A.T1-B.T2) AS TOTAL
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE) AS A
JOIN #K B ON A.CODE=B.CODE
报错: 列名 'CODE' 无效。

RINK_1 2019-07-18

打赏
举报

先下面的SELECT看看对不对，如果对的，再用后面的UPDATE试试


SELECT SUM(A.T1-B.T2) AS TOTAL
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE

UPDATE #K
SET T3=A.TOTAL
FROM
(SELECT SUM(A.T1-B.T2) AS TOTAL
FROM #K A
OUTER APPLY (SELECT T2 FROM #K WHERE A.CODE=CODE AND SORT<=A.SORT AND SORT>A.SORT-10) AS B
GROUP BY A.CODE) AS A
JOIN #K B ON A.CODE=B.CODE

唐诗三百首 2019-07-18

打赏
举报


  update a
   set a.T3=(select sum(b.t1-a.t2) 
             from #k b 
             where b.code=a.code and b.sort<=a.sort and b.sort>a.sort-10)
   from #k a