我有一个名为字典的数据库表,包含所有字典条目的以下字段:
public static final String COLUMN_NAME_UID = "_id_";
public static final String COLUMN_NAME_WORD = "word";
public static final String COLUMN_NAME_WORD = "wordSorted";
public static final String COLUMN_NAME_WORD_LENGTH = "length";
public static final String COLUMN_NAME_COUNT_A = "count_A";
public static final String COLUMN_NAME_COUNT_B = "count_B";
public static final String COLUMN_NAME_COUNT_C = "count_C";
public static final String COLUMN_NAME_COUNT_D = "count_D";
public static final String COLUMN_NAME_COUNT_E = "count_E";
public static final String COLUMN_NAME_COUNT_F = "count_F";
public static final String COLUMN_NAME_COUNT_G = "count_G";
public static final String COLUMN_NAME_COUNT_H = "count_H";
public static final String COLUMN_NAME_COUNT_I = "count_I";
public static final String COLUMN_NAME_COUNT_J = "count_J";
public static final String COLUMN_NAME_COUNT_K = "count_K";
public static final String COLUMN_NAME_COUNT_L = "count_L";
public static final String COLUMN_NAME_COUNT_M = "count_M";
public static final String COLUMN_NAME_COUNT_N = "count_N";
public static final String COLUMN_NAME_COUNT_O = "count_O";
public static final String COLUMN_NAME_COUNT_P = "count_P";
public static final String COLUMN_NAME_COUNT_Q = "count_Q";
public static final String COLUMN_NAME_COUNT_R = "count_R";
public static final String COLUMN_NAME_COUNT_S = "count_S";
public static final String COLUMN_NAME_COUNT_T = "count_T";
public static final String COLUMN_NAME_COUNT_U = "count_U";
public static final String COLUMN_NAME_COUNT_V = "count_V";
public static final String COLUMN_NAME_COUNT_W = "count_W";
public static final String COLUMN_NAME_COUNT_X = "count_X";
public static final String COLUMN_NAME_COUNT_Y = "count_Y";
public static final String COLUMN_NAME_COUNT_Z = "count_Z";
我希望能够搜索实例 test* 并找到由 "t"、"e"、"s"、"t" 和通配符组成的所有单词,例如 "tests"(s 是通配符)、"setts"(s 是通配符)、"set"、"tet"、"es"、"te"、"best"(b 是通配符)等。任何使用这些字母组合而成的词汇都可以。
我尝试过像这样的方法,但是这个示例只能找到没有通配符的四个字母的单词:
SELECT * FROM dictionary WHERE
count_E=1 AND
count_S=1 AND
count_T=2
SELECT * FROM dictionary WHERE length <=4
这将产生以下结果:
"137075" "sett" "estt"
"145808" "stet" "estt"
"153675" "test" "estt"
"153851" "tets" "estt"
现在,我知道,这实际上是一个离散数学问题。
以下是如何获取所有带有一个空格并使用上一次查询提供的所有字母的五个字母单词:
SELECT * FROM dictionary WHERE
count_E=1 AND
count_S=1 AND
count_T=2
INTERSECT
SELECT * FROM dictionary WHERE length <=5
结果:
"97705" "netts" "enstt"
"137075" "sett" "estt"
"145250" "state" "aestt"
"145808" "stet" "estt"
"152303" "taste" "aestt"
"152333" "tates" "aestt"
"152632" "teats" "aestt"
"153361" "tents" "enstt"
"153675" "test" "estt"
"153676" "testa" "aestt"
"153733" "testy" "estty"
"153769" "teths" "ehstt"
"153851" "tets" "estt"
"153874" "texts" "esttx"
"156575" "totes" "eostt"
"157952" "trets" "erstt"
"172060" "yetts" "estty"
然而,要找到所有隐藏的子单词,我必须遍历所有字母的组合......有人能帮我想出一种更优雅的方法来从查询中查找变位词和子单词以及最多两个通配符吗?我也知道可以在SQL中使用REGEXP,所以那可能是一种方法。我现在不知道,所以我把这个问题提交给大家...是否有任何查询、交集、连接等可以帮助我解决这个问题?
更新:我认为我可能已经偶然发现了这一点,但我不确定它是否正常工作。如果有帮助,请告诉我。
SELECT * FROM dictionary WHERE
(
count_E<=1 AND
count_S<=1 AND
count_T<=1
)
INTERSECT SELECT * FROM dictionary WHERE length =(count_E+count_S+count_T+1) ORDER BY length
+1是为了计算一个空格。对于两个空格,我考虑只做+2等等... +0将只是这些字母,以及您可以从它们中制作的任何东西。