用CSV计数使用SQL Server 2008 R2
问题描述:
我想统计在csv列下的记录数量,并且也希望显示它们不同。用CSV计数使用SQL Server 2008 R2
例:
表:
create table test1
(
cola int,
colb varchar(10)
)
一些记录:
insert into test1 values(1,'SAM');
insert into test1 values(2,'SAM');
insert into test1 values(3,'JACK');
insert into test1 values(4,'MAK');
脚本:
with cte (colb)
AS
(
SELECT SUBSTRING(
(SELECT ',' + s.colb
FROM test1 as s
ORDER BY s.colb
FOR XML PATH('')),2,200) AS CSV
)
select count(*) as NumberOfNames,colb as Names
from cte
group by colb
结果:
NumberOfNames Names
-----------------------------------
1 JACK,MAK,SAM,SAM
预期结果:
NumberOfNames Names
-----------------------------------
3 JACK,MAK,SAM
答
你的CTE只有串接名称,也不会指望他们。 根据您的例子中,查询可能是这样的:
SELECT COUNT(DISTINCT colb) AS NumberOfNames,
(SELECT STUFF((SELECT ',' + s.colb FROM test1 as s ORDER BY s.colb FOR XML PATH('')), 1, 1, '')) AS CSV
FROM test1
结果:
NumberOfNames CSV
------------- ----------------
3 JACK,MAK,SAM,SAM
在使用STUFF函数删除第一个逗号。
答
这个怎么样....
Declare @tempVariable as Varchar(100)
Select @tempVariable= coalesce(@tempVariable+',','')+ colb
from
(
Select distinct colb from test1
) as te
Select len(@tempVariable) - len(replace(@tempVariable, ',', ''))+1, @tempVariable
结果:
太谢谢你了。 – Meem 2014-10-03 04:36:21