sql-server - 如何在 SQL Server 中计算表的所有三元组

Question

我有一张类似的 table

一
二
三
四
五
六
七

我需要一个具有类似输出的表的函数

一二树
二三四
三四五
...

Wikipedia page on n-gram

Answer 1

在 MySQL 中测试。首先创建数据:

create table words(id integer auto_increment primary key, text varchar(255));
insert into words (text) values ('one');
insert into words (text) values ('two');
insert into words (text) values ('three');
insert into words (text) values ('four');
insert into words (text) values ('five');
insert into words (text) values ('six');
insert into words (text) values ('seven');

查询:

select
    t1.text as tx1,
    t2.text as tx2,
    t3.text as tx3
from
    words as t1,
    words as t2,
    words as t3
where
    (t1.id + 1) = t2.id
    and
    (t2.id + 1) = t3.id
;

它依赖于按顺序排列的单词 ID。字符串连接函数可用于将 3 列合并为一列。

select
    concat(t1.text, '-',
           t2.text, '-',
           t3.text)
    as trigram
from
    words as t1,
    words as t2,
    words as t3
where
    (t1.id + 1) = t2.id
    and
    (t2.id + 1) = t3.id
;