22,302
社区成员




----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2015-11-26 17:48:53
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(8))
insert [tb]
select 'aa' union all
select 'bb' union all
select 'aa,bb' union all
select 'bb,cc' union all
select 'bb,cc,dd'
--------------开始查询--------------------------
SELECT col,COUNT(1) AS num
FROM
(
Select
COl=substring(a.Col,b.number,charindex(',',a.Col+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col)
where
substring(','+a.COl,b.number,1)=',') AS t
GROUP BY col
----------------结果----------------------------
/* col num
-------- -----------
aa 2
bb 4
cc 2
dd 1
(4 行受影响)
*/