note表
id content taglist
1 test 2,3
2 test1 1,3
tag表
tagid tagname
1 a
2 b
3 c
我要得到的数据是：
tagid tagname count
2 b 1
3 c 2
这个怎么查询啊！
------解决方案--------------------
create table note(id varchar(10), content varchar(10),taglist varchar(10))
insert into note values( '1 ', 'test ' , '2,3 ')
insert into note values( '2 ', 'test1 ', '1,3 ')
create table tag(tagid varchar(10), tagname varchar(10))
insert into tag values( '1 ', 'a ')
insert into tag values( '2 ', 'b ')
insert into tag values( '3 ', 'c ')
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
select tag.* , m.cnt from tag,
(
select taglist , count(*) cnt from
(
SELECT A.ID, taglist = SUBSTRING(A.taglist, B.ID, CHARINDEX( ', ', A.taglist + ', ', B.ID) - B.ID) FROM note A, # B WHERE SUBSTRING( ', ' + a.taglist, B.id, 1) = ', '
) t
group by taglist
) m
where tag.tagid = m.taglist
order by tag.tagid
drop table note,tag,#
/*
tagid tagname cnt
---------- ---------- -----------
1 a 1
2 b 1
3 c 2
（所影响的行数为 3 行）
*/
------解决方案--------------------
----创建测试数据
declare @note table(id int, content varchar(10), taglist varchar(10))
insert @note
select 1, 'test ', '2,3 ' union all
select 2, 'test1 ', '1,3 '
declare @tag table(tagid int, tagname varchar(10))
insert @tag
select 1, 'a ' union all
select 2, 'b ' union all
select 3, 'c '
----查询
SELECT a.tagid ,a.tagname,count(*) as [count]
FROM @tag as a INNER JOIN @note as b on charindex( ', ' + rtrim(tagid) + ', ', ', ' + taglist + ', ') > 0
GROUP BY a.tagid,a.tagname
/*结果：
tagid tagname count
----------- ---------- -----------
1 a 1
2 b 1
3 c 2
*/