我有一个称为字典的数据库表，当前具有用于所有字典条目的以下字段:
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"
现在，我知道，这本质上是一个琐碎的数学问题。
这是我如何用一个空格获取所有5个字母的单词以及上一个查询中提供的所有字母的方法:
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就是那些字母，以及您可以从中得到的任何东西。
最佳答案
您必须执行以下操作，将表的所有字段组合在一起，如下所示:
concatenacion = "(_id||' '||Desc_art||' '||Nom_proveedor||' '||marca) like '"+resultado+"'" +
"OR (_id||' '||Nom_proveedor||' '||marca||' '||Desc_art) like '"+resultado+"'" +
"OR (marca||' '||Nom_proveedor||' '||Desc_art||' '||_id) like '"+resultado+"'" +
"OR (marca||' '||Nom_proveedor||' '||_id||' '||Desc_art) like '"+resultado+"'" +
"OR (Desc_art||' '||Nom_proveedor||' '||marca||' '||_id) like '"+resultado+"'" +
"OR (Desc_art||' '||_id||' '||Nom_proveedor||' '||marca) like '"+resultado+"'";
然后发出您的请求，并且在WHERE子句中必须放入您的锥状石蒜，例如:
cursor=bd.rawQuery("select _id, Desc_art, cant_art, Desc_bulto, precio"+getDefaultNroLista(codcliente)+", tiene_imagen,marca from listas_precios where "+concatenacion+" ORDER BY Desc_art ASC", null);
我为我工作得很好，希望能为您服务