我有两个表:

1) Employee -- _id, employee_name.
2) Salary -- _id, amount, emp_id.

示例数据:

Employee:
1 John
2 Rocky
3 Marry

Salary:

1 500 1 //salary for John
2 400 1 //salary for John
3 600 2 //salary for Rocky
4 700 2 //salary for Rocky
5 350 3 //salary for Marry

现在，我想在工资表中搜索以查看我支付了工资的人。假设如果我在薪水表中搜索“John”，它应该返回 John 的第 1 行和第 2 行。

这是我正在尝试的:

String where = " emp_id in (select _id from Employee where employee_name like ? )";

String[] whereArgs = new String[] {"'%" + mFilter + "%'" };

Cursor c = getDB(mContext).query("Salary", null, where, whereArgs,
                null, null, null);

但它总是没有返回结果。请帮忙。

更新

我调试了代码，发现游标中正在执行以下查询:

SELECT * FROM Salary WHERE emp_id in (select _id from Employee where employee_name like ? );

最佳答案

选择参数自动用作字符串。改变这个:

String[] whereArgs = new String[] {"'%" + mFilter + "%'" };

为此:

String[] whereArgs = new String[] {"%" + mFilter + "%" }; (removed ' from your strings)

有了额外的 '，它会取消文本并变成 ''%John%'' 构建器会自动处理 '选择参数。

编辑
也将您的查询更改为此:

String sql = "SELECT * FROM Salary WHERE emp_id in (select _id from Employee where employee_name like ?)";
Cursor c = getDB(mContext).rawQuery(sql, whereArgs);

编辑 2

我用下面的类重新创建了您的设置，我的所有代码都运行良好。我从用户 John 那里得到了两个结果。我认为问题出在您的数据库创建中，或者您的数据库中没有数据。使用 DDMS 拉取数据库并使用 SQLite 浏览器打开它。检查您的数据库中是否有任何数据。如果是，则您的表创建类型与 select 语句不匹配。当我调用 GetMyValues() 时，我从游标返回了 2 条记录。

public class DataBaseHandler extends SQLiteOpenHelper {

    private static final String TAG = "DBHandler";

    //Database VERSION
    private static final int DATABASE_VERSION = 2;

    //DATABASE NAME
    private static final String DATABASE_NAME = "test";

    //DATABASE TABLES
    private static final String TABLE_SALARY = "Salary";
    private static final String TABLE_EMP = "Employee";

    //DATABASE FIELDS
    private static final String SalaryID= "_id";
    private static final String SalaryEmpName = "employee_name";
    private static final String EmpID= "_id";
    private static final String EmpAmt = "amount";
    private static final String EmpSalID = "emp_id";

    //DATABASE TYPES
    private static final String INTPK = "INTEGER PRIMARY KEY";
    private static final String INT = "INTEGER";
    private static final String TEXT = "TEXT";

    //CREATE TABLES
    private static final String CREATE_SALARY_TABLE = "CREATE TABLE " + TABLE_SALARY + "("
                + EmpID + " " + INTPK + "," +  EmpAmt + " " + INT + ","
                + EmpSalID + " " + INT + ")";

      //CREATE TABLE Salary(_id INTEGER PRIMARY KEY,amount INTEGER,emp_id INTEGER)

    private static final String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMP + "("
                + SalaryID + " " + INTPK + "," + SalaryEmpName + " " + TEXT + ")";

      //CREATE TABLE Employee(_id INTEGER PRIMARY KEY, employee_name TEXT)

    public DataBaseHandler(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_EMPLOYEE_TABLE);
        db.execSQL(CREATE_SALARY_TABLE);

        insertEmployeeValues(db);
        insertSalaryValues(db);     
    }

    private void insertEmployeeValues(SQLiteDatabase db){
        ContentValues values = new ContentValues();
        values.put(SalaryEmpName, "John");
        db.insert(TABLE_EMP, null, values);
        values.clear();
        values.put(SalaryEmpName, "Rocky");
        db.insert(TABLE_EMP, null, values);
        values.clear();
        values.put(SalaryEmpName, "Marry");
        db.insert(TABLE_EMP, null, values);
        values.clear();
    }

    private void insertSalaryValues(SQLiteDatabase db){
        ContentValues values = new ContentValues();
        values.put(EmpAmt, 500);
        values.put(EmpSalID, 1);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 400);
        values.put(EmpSalID, 1);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 600);
        values.put(EmpSalID, 2);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 700);
        values.put(EmpSalID, 2);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 350);
        values.put(EmpSalID, 3);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMP);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SALARY);
        onCreate(db);
    }

    public int GetMyValues(){
        String mFilter = "John"; 
        String[] whereArgs = new String[]{"%" + mFilter + "%"};
        int count = 0;
        SQLiteDatabase db = this.getWritableDatabase();
        String where = " emp_id in (select _id from Employee where employee_name like ? )";
        Cursor c = db.query("Salary",null, where, whereArgs,null,null,null);
        count = c.getCount();
        c.close();
        return count;
    }
}

Dev Reference:

You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings

关于android - 在 android sqlite 中使用 IN 关键字，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17985032/