表A: name hits
aa 12
bb 85
cc 69
dd 56现在要写个 sql 得到表B name hits allhits
aa 12 222
bb 85 222
cc 69 222
dd 56 222
其中hits是新加的一列 222是前面hits的和 我现在要都放在各个记录后面
aa 12
bb 85
cc 69
dd 56现在要写个 sql 得到表B name hits allhits
aa 12 222
bb 85 222
cc 69 222
dd 56 222
其中hits是新加的一列 222是前面hits的和 我现在要都放在各个记录后面
from a
from 表A t
from a,(select sum(hits)col from tb) b
set @cnt = select sum(hits) from 表Aselect name, hits, @cnt as allhits from 表A
go
create table [ta] (name nvarchar(4),hits int)
insert into [ta]
select 'aa',12 union all
select 'bb',85 union all
select 'cc',69 union all
select 'dd',56
select *
,(select sum(hits) from ta)allhits
from ta
/*
name hits allhits
---- ----------- -----------
aa 12 222
bb 85 222
cc 69 222
dd 56 222(4 個資料列受到影響)*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([name] varchar(2),[hits] int)
insert [A]
select 'aa',12 union all
select 'bb',85 union all
select 'cc',69 union all
select 'dd',56
---查询---
select a.*,b.col
from a,(select sum(hits)col from A) b---结果---
name hits col
---- ----------- -----------
aa 12 222
bb 85 222
cc 69 222
dd 56 222(所影响的行数为 4 行)
insert [A]
select 'aa',12 union all
select 'bb',85 union all
select 'cc',69 union all
select 'dd',56select name , hits , allhits = (select sum(hits) from a) from adrop table a/*
name hits allhits
---- ----------- -----------
aa 12 222
bb 85 222
cc 69 222
dd 56 222(所影响的行数为 4 行)
*/