34,593
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO tt
SELECT 'BB','A1'
ALTER FUNCTION func_test
()
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @s VARCHAR(max)
DECLARE @t1 VARCHAR(100),@lt1 VARCHAR(100),@b bit
-- SELECT @s=ISNULL(@s+',','')+tt.t1 FROM tt GROUP BY t1 HAVING COUNT(t2)>1
DECLARE cur CURSOR FOR
SELECT tt.t1 FROM tt ORDER BY t1
OPEN cur
FETCH NEXT FROM cur INTO @t1
WHILE @@FETCH_STATUS=0
BEGIN
IF @t1=@lt1 AND @b=0
BEGIN
SET @s=ISNULL(@s+',','')+@t1
SET @b=1
END
ELSE
SET @b=0
SET @lt1=@t1
FETCH NEXT FROM cur INTO @t1
END
CLOSE cur
DEALLOCATE cur
RETURN @s
END
GO
CREATE TABLE tt(T1 VARCHAR(100),T2 VARCHAR(100))
INSERT INTO tt
SELECT 'AA','A3' UNION ALL
SELECT 'BB','A1' UNION ALL
SELECT 'BB','A1' UNION ALL
SELECT 'CC','A2' UNION ALL
SELECT 'DD','A1' UNION ALL
SELECT 'DD','A2'
DECLARE @s VARCHAR(max)
SELECT @s=ISNULL(@s+',','')+tt.t1 FROM tt GROUP BY t1 HAVING COUNT(t2)>1
RETURN @s
如果是为了学习使用光标,可以这样试试,光标操作那部分用注释掉的一行语句也行
CREATE FUNCTION func_test
()
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @s VARCHAR(max)
DECLARE @t1 VARCHAR(100),@lt1 VARCHAR(100)
-- SELECT @s=ISNULL(@s+',','')+tt.t1 FROM tt GROUP BY t1 HAVING COUNT(t2)>1
DECLARE cur CURSOR FOR
SELECT tt.t1 FROM tt ORDER BY t1
OPEN cur
FETCH NEXT FROM cur INTO @t1
WHILE @@FETCH_STATUS=0
BEGIN
IF @t1=@lt1 SET @s=ISNULL(@s+',','')+@t1
SET @lt1=@t1
FETCH NEXT FROM cur INTO @t1
END
CLOSE cur
DEALLOCATE cur
RETURN @s
END
GO
SELECT dbo.func_test()
--BB,DD
SELECT t1
FROM @table_1;
数据库不支持这样的写法
只能用动态语句函数不支持,改用存储过程去调用
EXEC('SELECT *
FROM '+@table_1);