PostgreSQL和文字游戏

SELECT word from
(select word,generate_series(0,length(word)) as s from good_words) as q
WHERE substring(word,s,1) IN ('t','h','e','l','t','r','s')
GROUP BY word
HAVING count(*)>=length(word);

http://sqlfiddle.com/#!1/2e3a2/3

编辑：

此查询仅选择有效单词,尽管看起来有点多余.它并不完美,但肯定证明它可以做到.

WITH words AS 
(SELECT word,substring(word,1) as sub from
(select word,generate_series(1,'s','o','m','a','d','s'))

SELECT w.word FROM
(
SELECT word,words.sub,count(DISTINCT s) as cnt FROM
(SELECT s,substring(array_to_string(l,''),1) as sub FROM
(SELECT l,generate_subscripts(l,1) as s FROM 
 (SELECT ARRAY['t','s'] as l) 
 as q) 
as q) as let JOIN
words ON let.sub=words.sub
GROUP BY words.word,words.sub) as let
JOIN
(select word,sub,count(*) as cnt from words
 GROUP BY word,sub)
as w ON let.word=w.word AND let.sub=w.sub AND let.cnt>=w.cnt
GROUP BY w.word
HAVING sum(w.cnt)=length(w.word);

为该图像提供所有可能的3个字母单词(485)：http://sqlfiddle.com/#!1/2fc66/1
摆弄699个单词,其中485个是正确的：http://sqlfiddle.com/#!1/4f42e/1

编辑2：
我们可以像这样使用数组运算符来获取包含我们想要的字母的单词列表：

SELECT word as sub from
(select word,length(word)) as s from good_words) as q
GROUP BY word
HAVING array_agg(substring(word,1)) <@ ARRAY['t','s'];

所以我们可以用它来缩小我们需要检查的单词列表.

WITH words AS 
(SELECT word,length(word)) as s from 
(
  SELECT word from
(select word,'s']
)as q) as q)
SELECT DISTINCT w.word FROM
(
SELECT word,sub)
as w ON let.word=w.word AND let.sub=w.sub AND let.cnt>=w.cnt
GROUP BY w.word
HAVING sum(w.cnt)=length(w.word) ORDER BY w.word;

http://sqlfiddle.com/#!1/4f42e/44

我们可以使用GIN索引来处理数组,这样我们就可以创建一个表来存储字母数组并使单词指向它(act,cat和tact都指向数组[a,c,t])所以可能这样可以加快速度,但这需要进行测试.