很有挑战性的问题~~分不多了~!SQL高手多帮忙吧~~

[dotcom2003]

表photo数据如下:
id tag
----------------
1 车,本田,思域
2 车,本田,思域
3 车,本田,思域
4 车,本田,思域
5 车,本田,思域
6 车,本田,思域
7 车,本田,思域
8 本田,思域
------------------------

怎么才能获得以下数据集:

ID TAG COUNT
-------------
1 车 7
2 本田 8
3 思域 8


就是把TAG字段拆开~然后计算哪条数据用到这个标签 计算出总数!

[dotcom2003]

咋不来人了啊~~~

[dotcom2003]

UP UP

[dotcom2003]

CREATE PROCEDURE dbo.usp_Photo_temp
AS
declare @tmpTbl table(id int, tag nvarchar(10))
declare @tmpTbl2 table(id int identity, tag nvarchar(10))
declare @intID int
declare @strInx int
declare @subStrInx int
declare @intStart int
declare @strFld nvarchar(10)
declare @tag nvarchar(50)

declare curSelData cursor for
select Targ from photo
where Targ is NOT NULL and len(targ) > 0
set @intID = 1

open curSelData

fetch next from curSelData into @tag

while(@@FETCH_STATUS = 0)
begin
set @tag = replace(ltrim(rtrim(@tag)), ' ', ',')
set @intStart = 1

while (1 = 1)
begin
set @strInx = charindex(',',@tag,@intStart)

if (@strInx = 0)
begin
set @strFld = right(@tag, len(@tag) - @intStart + 1)
insert into @tmpTbl values(@intID,@strFld)
set @intID = @intID + 1
break

end
else
begin
set @strFld = substring(@tag, @intStart, @strInx - @intStart)
insert into @tmpTbl values(@intID,@strFld)
set @intStart = @strInx + 1
set @intID = @intID + 1
end
end


fetch next from curSelData into @tag
end

close curSelData
deallocate curSelData

insert into @tmpTbl2(tag)
select distinct tag from @tmpTbl

select * from @tmpTbl2 where tag<>''


这个是一为高手帮我写的 只实现了拆分 但是没有计算出COUNT
明白的朋友帮忙改下也可以!!

[lzhs]

CREATE TABLE #photo(id int ,tag nvarchar(10))
INSERT INTO #photo
SELECT 1,N'车,本田,思域'
UNION
SELECT 2,N'车,本田,思域'
UNION
SELECT 3,N'车,本田,思域'
UNION
SELECT 4,N'车,本田,思域'
UNION
SELECT 5,N'车,本田,思域'
UNION
SELECT 6,N'车,本田,思域'
UNION
SELECT 7,N'车,本田,思域'
UNION
SELECT 8,N'本田,思域'


CREATE TABLE #Temp(id int identity(1,1),tag nvarchar(10))
CREATE UNIQUE INDEX unq_Idx_Temp_tag ON #Temp(tag) WITH(IGNORE_DUP_KEY = ON)


DECLARE @tag nvarchar(10),@subtag nvarchar(10)

DECLARE cur CURSOR FOR
SELECT tag FROM #photo

OPEN cur
FETCH cur INTO @tag
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE CHARINDEX(',',@tag) > 0
BEGIN
SELECT @subtag = SUBSTRING(@tag,1,CHARINDEX(',',@tag)-1)
,@tag = RIGHT(@tag,LEN(@tag)-CHARINDEX(',',@tag))
INSERT INTO #Temp(tag) VALUES(@subtag)
END

SELECT @subtag = @tag
INSERT INTO #Temp(tag) VALUES(@subtag)
FETCH cur INTO @tag
END

CLOSE cur
DEALLOCATE cur

--SELECT * FROM #Temp

SELECT t.ID,t.tag,COUNT(1) cnt
FROM #temp t
INNER JOIN #photo p ON CHARINDEX(t.tag,p.tag) > 0
GROUP BY t.ID,t.tag

DROP TABLE #Temp
DROP TABLE #photo

[lzhs]

CREATE UNIQUE INDEX unq_Idx_Temp_tag ON #Temp(tag) WITH(IGNORE_DUP_KEY = ON)

=================================================================================

这是2005的语法，如果是2000，用：

CREATE UNIQUE INDEX unq_Idx_Temp_tag ON #Temp(tag) WITH IGNORE_DUP_KEY

[分治实践]

写了一个,看能不能帮得上楼主
id tag
----------------
1 车,本田,思域
2 车,本田,思域
3 车,本田,思域
4 车,本田,思域
5 车,本田,思域
6 车,本田,思域
7 车,本田,思域
8 本田,思域

CREATE TABLE #temp(pkID INT,tag VARCHAR(100))
INSERT INTO #temp(pkID,tag) VALUES(1,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(2,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(3,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(4,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(5,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(6,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(7,'车,本田,思域')
INSERT INTO #temp(pkID,tag) VALUES(8,'本田,思域')


--SELECT * FROM #temp

--创建临时表,用于保存拆分后的TAG
CREATE TABLE #temp1(tag VARCHAR(20))

DECLARE @pkID INT
DECLARE @tag VARCHAR(20)
DECLARE cursor1 CURSOR FOR SELECT pkID,tag FROM #temp
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @pkID,@tag
WHILE(@@FETCH_STATUS = 0) BEGIN

--将Tag拆分,并插入临时表
INSERT INTO #temp1(tag)
SELECT * FROM dbo.splitstr(@tag,',')
--select * from dbo.splitstr('1,2,3,4',',')

FETCH NEXT FROM cursor1 INTO @pkID,@tag
END
CLOSE cursor1
DEALLOCATE cursor1

--统计
SELECT tag,COUNT(tag) FROM #temp1 GROUP BY tag

此方法依赖于下面的函数:
CREATE function splitstr(@SourceSql varchar(8000),@StrSeprate varchar(5))
returns @temp table(F1 varchar(2000))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'' insert @temp values(@SourceSql)
return
end

得出结果:
本田 8
思域 8
车 7

[fcuandy]

借用楼上的split函数和表

declare @s varchar(4000)
select @s=''
select @s=@s + tag + ',' from tb
select @s=stuff(@s,len(@s),1,'')
select f1,count(*) from dbo.split(@s,',') group by f1

[bill024]

create table t (id int,tag varchar(500))
insert t select 1,'车,本田,思域'
union all select 2,'车,本田,思域'
union all select 3,'车,本田,思域'
union all select 4,'车,本田,思域'
union all select 5,'车,本田,思域'
union all select 6,'车,本田,思域'
union all select 7,'车,本田,思域'
union all select 8,'本田,思域'

select * into #t from
(
select case when charindex('车',tag)>0 then '车' end as tag1 ,
case when charindex('本田',tag)>0 then '本田' end as tag2,
case when charindex('思域',tag)>0 then '思域' end as tag3
from t
)a

create table #t2(id int identity(1,1),tag varchar(50),count int)
insert into #t2
select tag,count from
(
select tag=tag1,count=count(tag1) from #t group by tag1
union all select tag2,count=count(tag2) from #t group by tag2
union all select tag3,count=count(tag3) from #t group by tag3
)a where tag is not null

select id,tag,count from #t2

drop table t
drop table #t
drop table #t2

id tag count
----------- -------------------------------------------------- -----------
1 车 7
2 本田 8
3 思域 8

[dotcom2003]

我是菜鸟 大侠多帮忙 贴些代码 不要只说思路~!!!

[dotcom2003]

UP一下~

[leo_lesley]

select (select count(1) from (
select TAG='车',[COUNT]=count(1) from t1 where charindex('车',tag)>0 union all
select TAG='本田',[COUNT]=count(1) from t1 where charindex('本田',tag)>0 union all
select TAG='思域',[COUNT]=count(1) from t1 where charindex('思域',tag)>0) t1 where t1.TAG<=t.TAG),* from (
select TAG='车',[COUNT]=count(1) from t1 where charindex('车',tag)>0 union all
select TAG='本田',[COUNT]=count(1) from t1 where charindex('本田',tag)>0 union all
select TAG='思域',[COUNT]=count(1) from t1 where charindex('思域',tag)>0 ) t

[dotcom2003]

不能这么写~~因为 车,本田,思域 不是固定的~!!!

[肥胖的柠檬]

关注....