这只是一个探索性的问题,看看是否有人做到了这一点,或者实际上这是完全可能的。
我们都知道标签云是什么,通常,标签云是由分配标签的人创建的。在SQL Server的当前功能中,是否有可能通过触发器在表中添加或更新记录时,通过查看特定列中的数据并获取流行的单词来自动创建它?
它类似于这个问题:How can I get the most popular words in a table via mysql?。但是,这是MySQL而不是MSSQL。
提前谢谢。詹姆斯
发布于 2011-10-07 00:27:27
下面是关于将分隔字符串解析为行的一个很好的例子:
http://anyrest.wordpress.com/2010/08/13/converting-parsing-delimited-string-column-in-sql-to-rows/
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
T-SQL: Opposite to string concatenation - how to split string into multiple records
如果要解析所有单词,可以使用空格‘’作为分隔符,然后每个单词占一行。
接下来,您只需通过单词GROUP选择结果集并聚合COUNT
对你的结果进行排序,你就到了那里。
发布于 2011-10-07 00:52:08
国际海事组织,设计方法使这一点变得困难。仅仅因为您允许用户分配标签并不意味着必须将标签存储为单个分隔的单词列表。您可以将该结构规范化为类似以下内容:
Create Table Posts ( Id ... not null primary key )
Create Table Tags( Id ... not null primary key, Name ... not null Unique )
Create Table PostTags
( PostId ... not null References Posts( Id )
, TagId ... not null References Tags( Id ) )现在你的问题变得微不足道了:
Select T.Id, T.Name, Count(*) As TagCount
From PostTags As PT
Join Tags As T
On T.Id = PT.TagId
Group By T.Id, T.Name
Order By Count(*) Desc如果您坚持将标记存储为分隔值,那么唯一的解决方案就是通过编写自定义的拆分函数来拆分值,然后进行计数。底部是拆分函数的一个示例。有了它,您的查询将如下所示(使用逗号分隔符):
Select Tag.Value, Count(*) As TagCount
From Posts As P
Cross Apply dbo.Split( P.Tags, ',' ) As Tag
Group By Tag.Value
Order By Count(*) Desc拆分函数:
Create Function [dbo].[Split]
(
@DelimitedList nvarchar(max)
, @Delimiter nvarchar(2) = ','
)
RETURNS TABLE
AS
RETURN
(
With CorrectedList As
(
Select Case When Left(@DelimitedList, DataLength(@Delimiter)/2) <> @Delimiter Then @Delimiter Else '' End
+ @DelimitedList
+ Case When Right(@DelimitedList, DataLength(@Delimiter)/2) <> @Delimiter Then @Delimiter Else '' End
As List
, DataLength(@Delimiter)/2 As DelimiterLen
)
, Numbers As
(
Select TOP (Coalesce(Len(@DelimitedList),1)) Row_Number() Over ( Order By c1.object_id ) As Value
From sys.objects As c1
Cross Join sys.columns As c2
)
Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
, Substring (
CL.List
, CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen
, Case
When CharIndex(@Delimiter, CL.list, N.Value + 1)
- CharIndex(@Delimiter, CL.list, N.Value)
- CL.DelimiterLen < 0 Then Len(CL.List)
Else CharIndex(@Delimiter, CL.list, N.Value + 1)
- CharIndex(@Delimiter, CL.list, N.Value)
- CL.DelimiterLen
End
) As Value
From CorrectedList As CL
Cross Join Numbers As N
Where N.Value < Len(CL.List)
And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
)https://stackoverflow.com/questions/7673399
复制相似问题