34,591
社区成员
发帖
与我相关
我的任务
分享
-- 普通方法,每一行都要进行运算
SELECT C1, C1 * 3 AS C3 FROM TableA
因为实际情况运算没这么简单了,所以请问能否在C1列相同的情况下只执行一次运算生成C3列?
-- 错误的方法
SELECT C1, C1 * 3 OVER(PARTITION BY C1) AS C3 FROM TableA
-- 普通方法,每一行都要进行运算
SELECT C1, C1 * 3 AS C3 FROM TableA
因为实际情况运算没这么简单了,所以请问能否在C1列相同的情况下只执行一次运算生成C3列?
-- 错误的方法
SELECT C1, C1 * 3 OVER(PARTITION BY C1) AS C3 FROM TableA
[/quote]
SELECT C1, C1 * 3 AS C3 FROM TableA group by C1
create table #t
(
c1 int,
c2 varchar(10)
)
insert into #t values (1,null)
insert into #t values (1,null)
insert into #t values (1,null)
insert into #t values (2,null)
insert into #t values (2,null)
insert into #t values (2,null)
;with cte as
(
select ROW_NUMBER()over(partition by c1 order by c2) as rn ,* from #t
)
update cte set c2=0 where rn=1
select * from #t
1 0
1 NULL
1 NULL
2 0
2 NULL
2 NULL