以下格式允许每个链接具有多个标记,并允许链接共享相同的标记。这使您可以轻松地查看哪些链接很受欢迎,同时避免存储重复的标记。
图式
Links
LinkID
Url
Name
Tags
TagID
Name
LinkTags
LinkID
TagID
样本数据:
Links
---------------------------------------------------
LinkID URL Name
1 http://avira.com/ Avira Antivirus
2 http://microsoft.com/ Microsoft
---------------------------------------------------
Tags
---------------------------------------------------
TagID Name
---------------------------------------------------
1 Software
2 Antivirus
3 Operating Systems
---------------------------------------------------
LinkTags
---------------------------------------------------
LinkID TagID
---------------------------------------------------
1 1
1 2
2 1
2 3
查询
链接和标记:
SELECT Links.*, GROUP_CONCAT(Tags.Name) AS TagList
FROM Links
LEFT JOIN LinkTags
ON LinkTags.LinkID = Links.LinkID
LEFT JOIN Tags
ON Tags.TagID = LinkTags.TagID
GROUP BY Links.LinkID
十个最流行的标签:
SELECT Tags.*
FROM LinkTags
LEFT JOIN Tags
ON Tags.TagID = LinkTags.TagID
GROUP BY LinkTags.TagID
ORDER BY (COUNT(LinkTags.LinkID)) DESC
LIMIT 10