表结构和数据如下所示:
id dName
----------- -----
1 a
4 a
2 b
3 b
5 c
6 c
要求使用一条查询语句查询出如下效果:
编号 列名 出现次数
------ ---- ---------
1 a 2
4 a 2
2 b 2
3 b 2
5 c 2
6 c 2
苦思许久,没结果,就写了个大致游标实现的方法,完善后如下
create function fun_show()
returns @tab table
(
列a int,
列b char(1),
列c int
)
as
begin
declare myCursor cursor for select dName from DBTable group by dName
open myCursor
declare @name char(1)
fetch next from myCursor into @name
while(@@fetch_status=0)
begin
declare @count int
--求的数量
select @count=count(*) from DBTable where dName=@name
declare myCur cursor for select id from DBTable where dName=@name
open myCur
declare @id int
fetch next from myCur into @id
while(@@fetch_status=0)
begin
insert into @tab values(@id,@name,@count)
fetch next from myCur into @id
end
close myCur
deallocate myCur
fetch next from myCursor into @name
end
close myCursor
deallocate myCursor
return
end