27,579
社区成员
发帖
与我相关
我的任务
分享
select tag, count(*)num from dt group by tag
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
tag int
)
go
--插入测试数据
insert into tb select 1,1
union all select 2,1
union all select 3,2
union all select 4,3
union all select 5,2
union all select 6,1
go
--代码实现
if object_id('test.dbo.f_str') is not null drop function f_str
go
create function dbo.f_str(@tag varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + rtrim(id) from tb where tag = @tag
return @str
end
go
--调用函数
select distinct tag,num=count(*),id=dbo.f_str(tag)
from tb
group by tag
/*测试结果
tag num id
---------------------
1 3 1,2,6
2 2 3,5
3 1 4
(3 行受影响)
*/
select
*
from
(select tag, num=count(*) from #temp group by tag) a
join
(select tag,id=stuff((select ',' + CAST(id as varchar(10)) from #temp where tag = a.tag for xml path('')), 1, 1, '')) b
on
a.tag=b.tag
create table #temp
(
id int,
tag int
)
insert #temp
select 1, 1union all
select 2, 1union all
select 3, 2union all
select 4, 3union all
select 5, 2union all
select 6, 1
go
--SQL:
select * from
(select tag, num=count(*) from #temp group by tag) a
cross apply
(select id=stuff((select ',' + CAST(id as varchar(10)) from #temp where tag = a.tag for xml path('')), 1, 1, '')) b
/*
tag num id
1 3 1,2,6
2 2 3,5
3 1 4
*/