我有一个表格,上面有我的用户的帖子。每个帖子只是一个文本字段,帖子可以包含标签,例如:
"Hello this is my first post and I am in #japan enjoying #tokio How are you doing?"
我想编写 MySQL 查询,该查询将遍历整个表并返回最流行的标签,其中包含按计数排序的 nr 次,例如:
#travel 53
#japan 14
#food 13
#tokio 6
#selfie 4
etc.
请注意,我没有单独的标签表。我所拥有的只是一个表格,上面有帖子,#tags 是帖子文本的一部分。
我们可以做这样的事情:
SELECT v.tag
, COUNT(1) AS cnt
FROM ( SELECT CONCAT('#',IF(t.foo=SUBSTRING_INDEX(t.foo,'#',i.n*-1),NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(t.foo,'#',i.n*-1),' ',1))) AS tag
FROM ( SELECT 1 AS n 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 UNION ALL SELECT 9 UNION ALL SELECT 10
UNION ALL SELECT 11 UNION ALL SELECT 12
) i
CROSS
JOIN ( SELECT 'Hello this is my first post and I am in #japan enjoying #tokio How are you doing?' AS foo
UNION ALL SELECT '#japan #rocks'
UNION ALL SELECT 'a # b ## c ##'
) t
HAVING tag IS NOT NULL
) v
GROUP BY v.tag
ORDER BY cnt DESC, v.tag ASC
内联视图i
需要扩展 13, 14, 15, ...最多可以出现在帖子中的最大标签数。
内联视图t
提供了一些示例行作为演示。内联视图应替换为表引用。
我们还需要指定在帖子中遇到#
和###
等字符串时应该返回什么。 (这些标签有效吗?这些应该被计算在内吗?
在 SQL 中,表引用可以替换为内联视图。
例如,假设我们有一个名为user_posts
的表,其中包含一个名为post_text
的字符类型列。假设我们有这个查询:
SELECT t.`post_text`
FROM `user_posts` t
我们可以将表引用 (user_posts
) 替换为返回结果集的查询,如下所示:
SELECT t.`post_text`
FROM (
SELECT 'Hi I''m in #tokio #japan' AS `post_text`
UNION ALL SELECT '#hanoi #rocks'
UNION ALL SELECT 'a # b ## c ##'
UNION ALL SELECT ''
) t
并不是说我们可以单独运行该内部查询,并让它返回结果集。当我们将其包装在括号中并将其放入外部查询的 FROM 子句中时,该查询的结果是外部查询的行源。
替换表引用的查询称为"内联视图"。MySQL称其为"派生表",主要是因为内联视图在MySQL查询计划中是如何处理的。