先建一个函数: create function getstr(@id int) returns varchar(100) as begin declare @c varchar(100) set @c='' select @c=@c+[desc] from t1 where id=@id return @c end语句: select id,dbo.getstr(id) as [desc] from t1 group by id
你的函数可以这样建立 CREATE FUNCTION DBO.GETSTR(@ID INT) RETURNS NVARCHAR(100) AS BEGIN DECLARE @RETURNSTR NVARCHAR(100) DECLARE @STR NVARCHAR(100) SELECT @RETURNSTR='' DECLARE GETSTR_CUR CURSOR FOR SELECT [DESC] FROM YOURTABLE WHERE [ID]=@ID OPEN GETSTR_CUR FETCH NEXT FROM GETSTR_CUR INTO @STR WHILE @@FETCH_STATUS <> -1 BEGIN SELECT @RETURNSTR=@RETURNSTR+LTRIM(RTRIM(@STR)) FETCH NEXT FROM GETSTR_CUR INTO @STR ENDEND_GETSTR_CUR: CLOSE GETSTR_CUR DEALLOCATE GETSTR_CUR RETURN @RETURNSTR END
To: gzhughie(hughie) 你用游标效率低多了,而且我和函数没错。如果是char字段,就改一个 select @c=@c+rtrim([desc]) from t1 where id=@id
create function getstr(@id int)
returns varchar(100)
as
begin
declare @c varchar(100)
set @c=''
select @c=@c+[desc] from t1 where id=@id
return @c
end语句:
select id,dbo.getstr(id) as [desc] from t1 group by id
CREATE FUNCTION DBO.GETSTR(@ID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @RETURNSTR NVARCHAR(100)
DECLARE @STR NVARCHAR(100) SELECT @RETURNSTR='' DECLARE GETSTR_CUR CURSOR
FOR SELECT [DESC]
FROM YOURTABLE
WHERE [ID]=@ID OPEN GETSTR_CUR FETCH NEXT FROM GETSTR_CUR INTO @STR
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @RETURNSTR=@RETURNSTR+LTRIM(RTRIM(@STR))
FETCH NEXT FROM GETSTR_CUR INTO @STR
ENDEND_GETSTR_CUR:
CLOSE GETSTR_CUR
DEALLOCATE GETSTR_CUR
RETURN @RETURNSTR
END
你用游标效率低多了,而且我和函数没错。如果是char字段,就改一个
select @c=@c+rtrim([desc]) from t1 where id=@id