求sql语句。。
tab A
name col1 col2 col3
aa 1 2 3
cc 3 2 5
bb 6 12 7
我想得到的为
tab B
name sum(col1+col2+col3) id
aa 6 3
cc 10 2
bb 25 1
请问怎么写哦
[解决办法]
SQL 2000:
DECLARE @Temp Table
(
Name varchar(10),
ColsSum int,
Id identity(int, 1, 1)
)
INSERT INTO @Temp(Name, ColsSum)
SELECT Name, Col1+Col2+Col3
FROM @Temp
ORDER BY Col1+Col2+Col3
SELECT * FROM @Temp
[解决办法]
select *,(select count(*) from (select name,sum(col1+col2+col3) col from test
group by name
) b where a.col <=b.col) from
(
select name,sum(col1+col2+col3) col from test
group by name
) a
order by 3 desc
[解决办法]
declare @temp table
(name char(30),
col int,
id int identity(1,1))
insert into @temp (name,col)
select name,sum(col1+col2+col3)
from lianxi group by name
select * from @temp order by id asc
