select id,text=text+from tablename a where a.id=b.id from tablename b group by id
请问 OpenVMS,你的a和b 是同一张表吗?我需要在同一张表中查找
select sum(合计的字段) from group by id 如果没有 相同的id时,它再累加也只有一个字段值的。^_*
如果ta为原表,建立一个结构相同的表tb,在查询分析器中如下:delete from tb insert into tb(id, text) select id, 'null' from ta group by id declare tbl_aa cursor for select id from tb order by id open tbl_aa declare @id char(10), @temp nvarchar(50) fetch next from tbl_aa into @id while(@@fetch_status=0) begin select @temp='' select @temp=rtrim(text)+@temp from ta where id=@id order by text desc update tb set text=@temp where id=@id fetch next from tbl_aa into @id end close tbl_aa deallocate tbl_aa select * from tb
字符型的数据也能用sum()合并吗?
该问题只能用嵌套游标来做:具体思路如下: 1、定义一个存储不同ID的游标 declare @id int declare @text text, @textSum text ----定义外层游标存储不同ID declare cur_cursor cursor for SELECT distinct id from table open cur_cursor fetch cur_cursor into @id while cur_cursor = 0 begin --定义内层游标存储当前ID的所有记录的text declare cur_cursor2 for select text from where id =@id open cur_cursor2 fetch cur_cursor2 into @text while cur_cursor2 = 0 begin set @textSum= @textSum+@text fetch cur_cursor2 into @text end close cur_cursor2 deallecote cur_cursor2 select @id , @textSum fetch cur_cursor into @id end close cur_cursor deallocate cur_cursor大概思路就是这样, 你不防试着做一下。
a和b是同一张表tablename的别名
不用游标CREATE FUNCTION getstr(@Id int) RETURNS Varchar(3000) AS BEGIN declare @getstr Varchar(3000) set @getstr='' select @getstr=@getstr+[text] from tscoladd where id=@id return(@getstr) END go select distinct id, dbo.getstr(id) from tscoladd b group by id order by id
from tablename b
group by id
如果没有 相同的id时,它再累加也只有一个字段值的。^_*
insert into tb(id, text) select id, 'null' from ta group by id
declare tbl_aa cursor
for
select id from tb order by id
open tbl_aa
declare @id char(10), @temp nvarchar(50)
fetch next from tbl_aa into @id
while(@@fetch_status=0)
begin
select @temp=''
select @temp=rtrim(text)+@temp from ta where id=@id order by text desc
update tb set text=@temp where id=@id
fetch next from tbl_aa into @id
end
close tbl_aa
deallocate tbl_aa
select * from tb
1、定义一个存储不同ID的游标
declare @id int
declare @text text, @textSum text
----定义外层游标存储不同ID
declare cur_cursor cursor
for
SELECT distinct id from table
open cur_cursor
fetch cur_cursor into @id
while cur_cursor = 0
begin
--定义内层游标存储当前ID的所有记录的text
declare cur_cursor2
for
select text from where id =@id
open cur_cursor2
fetch cur_cursor2 into @text
while cur_cursor2 = 0
begin
set @textSum= @textSum+@text
fetch cur_cursor2 into @text
end
close cur_cursor2
deallecote cur_cursor2
select @id , @textSum
fetch cur_cursor into @id
end
close cur_cursor
deallocate cur_cursor大概思路就是这样, 你不防试着做一下。
RETURNS Varchar(3000)
AS
BEGIN
declare @getstr Varchar(3000)
set @getstr=''
select @getstr=@getstr+[text] from tscoladd where id=@id
return(@getstr)
END
go
select distinct id, dbo.getstr(id)
from tscoladd b
group by id order by id