从包含多个标记的列值中提取并显示最常用的单个标记。

Question

下面是我创建的表，我还插入了值：

CREATE TABLE Tag (
    Tag_Name NVARCHAR(50)
)

INSERT INTO Tag (Tag_Name)
VALUES('<wedding-crashers>')
INSERT INTO Tag (Tag_Name)
VALUES('<analysis><star-wars>')
INSERT INTO Tag (Tag_Name)
VALUES('<comedy><the-pink-panther>')
INSERT INTO Tag (Tag_Name)
VALUES('<plot-explanation><analysis><ending><tree-of-life>')
INSERT INTO Tag (Tag_Name)
VALUES('<plot-explanation><the-departed>')
INSERT INTO Tag (Tag_Name)
VALUES('<star-wars><darth-vader><casting>')
INSERT INTO Tag (Tag_Name)
VALUES('<analysis><shutter-island>')
INSERT INTO Tag (Tag_Name)
VALUES('<tree-of-life>')
INSERT INTO Tag (Tag_Name)
VALUES('<analysis><tree-of-life>')
INSERT INTO Tag (Tag_Name)
VALUES('<inception><existenz>')
INSERT INTO Tag (Tag_Name)
VALUES('<effects><melancholia>')
INSERT INTO Tag (Tag_Name)
VALUES('<hollywood><iranian>')
INSERT INTO Tag (Tag_Name)
VALUES('<plot-explanation><pontypool>')
INSERT INTO Tag (Tag_Name)
VALUES('<the-matrix><sequels><plot-explanation>')
INSERT INTO Tag (Tag_Name)
VALUES('<editing><cut><metropolis>')
INSERT INTO Tag (Tag_Name)
VALUES('<remake><akira>')
INSERT INTO Tag (Tag_Name)
VALUES('<plot-explanation><ending><blue-valentine>')
INSERT INTO Tag (Tag_Name)
VALUES('<star-wars><plot-explanation>')
INSERT INTO Tag (Tag_Name)
VALUES('<plot-explanation><saving-private-ryan>')
INSERT INTO Tag (Tag_Name)
VALUES('<film-industry>')
INSERT INTO Tag (Tag_Name)
VALUES('<analysis><pulp-fiction>')
INSERT INTO Tag (Tag_Name)
VALUES('<analysis><back-to-the-future><time-travel>')
INSERT INTO Tag (Tag_Name)
VALUES('<analysis><quentin-tarantino><film-techniques><kill-bill>')
INSERT INTO Tag (Tag_Name)
VALUES('<alfred-hitchcock><psychological-thriller><vertigo>')
INSERT INTO Tag (Tag_Name)
VALUES('<production><bridesmaids>')
INSERT INTO Tag (Tag_Name)
VALUES('<star-wars>')
INSERT INTO Tag (Tag_Name)
VALUES('<book-adaptation><harry-potter>')
INSERT INTO Tag (Tag_Name)
VALUES('<film-techniques><animation>')
INSERT INTO Tag (Tag_Name)
VALUES('<remake><fantomas>')
INSERT INTO Tag (Tag_Name)
VALUES('<muppets>')
INSERT INTO Tag (Tag_Name)
VALUES('<muppets>')

现在的问题是：

哪一个标签使用最频繁？

为了取得这一结果，我曾尝试过：

SELECT Tag_Name AS 'Single most frequent Tag'
    , MAX([Tag Count]) AS 'Maximum Tag Count'
FROM (
    SELECT Tag_Name,COUNT(Tag_Name) AS 'Tag Count'
    FROM Tag
    GROUP BY Tag_Name
) A
GROUP BY Tag_Name
ORDER BY [Maximum Tag Count] DESC

我得到的结果如下：

​

​

这个结果的问题在于它显示了木偶是使用最频繁的标记。这是因为查询将木偶识别为其中只有单个标记的唯一列值。

如果您从屏幕截图中看到，可能plot-explanation或星战是使用最频繁的标记，但它们不是单独使用的，这就是我的查询无法识别和隔离它们的原因。

我试图实现的是提取最常用的标记，如plot-explanation或星战，并用一行显示结果，如下所示：

Single most common Tag  Maximum Tag Count
<plot-explanation>      26

我试图用我的查询实现这个结果，但是我的查询无法提取或隔离一个标签，比如plot-explanation或星战。相反，它只识别木偶，这是因为木偶是自己的，并且在两个不同的行中被自己重复。

怎么解决这个问题呢？

Answer 1

您应该将数据规范化，并表示每个记录的一个标记。使用此表定义：

CREATE TABLE Tag (
    ID int Tag_Group NOT NULL,
    Tag_Name NVARCHAR(50)
)

下面是插入脚本的第一部分，更新后每个记录只有一个标记：

INSERT INTO Tag (ID, Tag_Name)
VALUES (1, '<wedding-crashers>');
INSERT INTO Tag (ID, Tag_Name)
VALUES (2, '<analysis>');
INSERT INTO Tag (ID, Tag_Name)
VALUES (2, '<star-wars>');
INSERT INTO Tag (ID, Tag_Name)
VALUES (3, '<comedy>');
INSERT INTO Tag (ID, Tag_Name)
VALUES (3, '<the-pink-panther>');

现在，要查找使用最多的标记，只需要一个简单的聚合查询：

SELECT TOP 1 WITH TIES Tag_Name
FROM Tag
GROUP BY Tag_Name
ORDER BY COUNT(*) DESC;