table1
col1 col2 col3
A 10 1000
A 20 1000
A 40 500
B 10 2000
B 20 1000
B 30 500
C 50 3000
C 70 1000
C 90 500
根据col1和col2,减去各自最大的值,table1数据更新为:
col1 col2 col3
A 10 500
A 20 500
A 40 0
B 10 1500
B 20 500
B 30 0
C 50 2500
C 70 500
C 90 0
如何实现?请大家都帮帮忙,谢谢了。
col1 col2 col3
A 10 1000
A 20 1000
A 40 500
B 10 2000
B 20 1000
B 30 500
C 50 3000
C 70 1000
C 90 500
根据col1和col2,减去各自最大的值,table1数据更新为:
col1 col2 col3
A 10 500
A 20 500
A 40 0
B 10 1500
B 20 500
B 30 0
C 50 2500
C 70 500
C 90 0
如何实现?请大家都帮帮忙,谢谢了。
from
(select col1,col2,col3,col22 = (select max(col2) from table1 where col1 = t1.col1)
from table1 t1
)a inner join table1 b
on a.col1 = b.col1 and a.col22 = b.col2
(select col1 , min(col3) as col3 from table1 group by col1) b
where a.col1 = b.col1
是根据col1减去各自col3最小的吧?select a.col1,a.col2,a.col3 - b.col3 as col3 from table1,
(select col1 , min(col3) as col3 from table1 group by col1) b
where a.col1 = b.col1
insert tb
select 'A', 10, 1000
union all select 'A', 20, 1000
union all select 'A', 40, 500union all select 'B', 10, 2000
union all select 'B', 20, 1000
union all select 'B', 30, 500union all select 'C', 50, 3000
union all select 'C', 70, 1000
union all select 'C', 90, 500select tb.col1, tb.col2, tb.col3-tmp2.col3 as col3 from tb
left join
(
select tb.* from tb
inner join(
select col1, max(col2) as col2 from tb
group by col1) tmp on tb.col1=tmp.col1 and tb.col2=tmp.col2
)tmp2 on tb.col1=tmp2.col1drop table tb
col1 col2 col3
---- ----------- -----------
A 10 500
A 20 500
A 40 0
B 10 1500
B 20 500
B 30 0
C 50 2500
C 70 500
C 90 0(9 row(s) affected)
insert table1
select 'A', 10, 1000
union all select 'A', 20, 1000
union all select 'A', 40, 500union all select 'B', 10, 2000
union all select 'B', 20, 1000
union all select 'B', 30, 500union all select 'C', 50, 3000
union all select 'C', 70, 1000
union all select 'C', 90, 500select a.col1,a.col2,a.col3 - b.col3 as col3 from table1 a,
(select col1 , min(col3) as col3 from table1 group by col1) b
where a.col1 = b.col1col1 col2 col3
---- ----------- -----------
A 10 500
A 20 500
A 40 0
B 10 1500
B 20 500
B 30 0
C 50 2500
C 70 500
C 90 0(所影响的行数为 9 行)
是根据col1,减去各自最小的值吧。UPDATE tb
SET tb.col3 = tb.col3 - A.col3
FROM tb
JOIN (SELECT col1,min(col3) as col3 FROM tb GROUP BY col1 )
A
ON tb.col1 = A.col1