仅当原始值为 0 时，带有数学函数的房间查询才有效

尝试

@Query("SELECT id,firm,model,aberration_correction FROM PERSON" + " WHERE round(CAST(aberration_correction AS REAL),2) = round(CAST(:sphecorneastr AS REAL),2)"

或：-

@Query("SELECT id,firm,model,aberration_correction FROM PERSON" + " WHERE round(aberration_correction,2) = round(:sphecorneastr,2)")

这应该转换字符串文字（因为 Room 会将值括在单引号中，因此“-0.2”）。

查看 https://www.sqlite.org/lang_expr.html#castexpr 查看 https://www.sqlite.org/lang_corefunc.html#round

双精度存储非常高，例如，-0.20 不会这样存储，而是存储为 -0.200000000000000093（或 -0.1950000000000000092）。

演示

也许考虑这个例子/演示：-

首先是 Person 类：-

@Entity
class Person {
    @PrimaryKey
    Long id=null;
    String firm;
    String model;
    String aberration_correction;
    double aberration_correction_double;

    Person(){}
    @Ignore
    Person(Long id,String firm, String model, double aberration_correction) {
        this.id  = id;
        this.firm = firm;
        this.model = model;
        this.aberration_correction = String.valueOf(aberration_correction);
        this.aberration_correction_double = aberration_correction;
    }
    @Ignore
    Person(String firm, String model, double aberration_correction) {
        this.id = null;
        this.firm = firm;
        this.model = model;
        this.aberration_correction = String.valueOf(aberration_correction);
        this.aberration_correction_double = aberration_correction;
    }
}

• 请注意，更正同时具有 double 和 String 表示形式（均基于 double）

带注释的抽象类（可以是接口）PersonDao：-@Dao

@Dao
abstract class PersonDao {
   @Insert(onConflict = OnConflictStrategy.IGNORE)
   abstract long insert(Person person);
   @Query("SELECT id,firm,model,aberration_correction, aberration_correction_double FROM PERSON" + " WHERE round(CAST(aberration_correction AS REAL),2) = round(CAST(:sphecorneastr AS REAL),2)")
   abstract List<Person> getPeopleByCorrection(String sphecorneastr);
   @Query("SELECT id,firm,model,aberration_correction, aberration_correction_double FROM PERSON" + " WHERE round(aberration_correction,2) = round(:sphecorneastr,2)")
   abstract List<Person> getPeopleByCorrectionV2(String sphecorneastr);
}

在活动中使用带有（方便和简洁）和以下代码的基本带注释的类：-@Database.allowMainThreadQueries

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    PersonDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getPersonDao();

        Double correction = 1.0;
        for (int i=0;i<1000;i++) {
            dao.insert(new Person("Firm" + i,"Model" + i,correction));
            correction = correction - 0.005;
        }

        correction = -0.20;
        for (Person p: dao.getPeopleByCorrection(correction.toString())) {
            Log.d("DBINFO","Firm is " + p.firm + " Model is " + p.model + " C1 is " + p.aberration_correction + " C2 is " + p.aberration_correction_double);
        }
        for (Person p: dao.getPeopleByCorrectionV2(correction.toString())) {
            Log.d("DBINFO","Firm is " + p.firm + " Model is " + p.model + " C1 is " + p.aberration_correction + " C2 is " + p.aberration_correction_double);
        }
    }
}

将插入 1000 行，校正值从 1.0 到接近 - 4.0，递减 0.005（从而覆盖 -0.20 值）。

然后使用这两个查询，显示不需要 CAST，但需要轮次。

当运行时（仅设计为运行一次），则日志包括：-

2022-09-25 07:27:42.838 D/DBINFO: Firm is Firm239 Model is Model239 C1 is -0.19500000000000092 C2 is -0.19500000000000092
2022-09-25 07:27:42.839 D/DBINFO: Firm is Firm240 Model is Model240 C1 is -0.20000000000000093 C2 is -0.20000000000000093


2022-09-25 07:27:42.841 D/DBINFO: Firm is Firm239 Model is Model239 C1 is -0.19500000000000092 C2 is -0.19500000000000092
2022-09-25 07:27:42.841 D/DBINFO: Firm is Firm240 Model is Model240 C1 is -0.20000000000000093 C2 is -0.20000000000000093

可以看出，在任何一种情况下，-0.20 都不是存储值，但通过四舍五入到 2 dp，即可获得接近的值。您可能需要调整舍入以适应。

查看存储的实际数据，通过应用程序检查，然后

最初的行是：-

最后一行是：-

-0.20 标记周围的行是：-

• 突出显示提取的行

其他（在提供实体和实际数据后）

问题 1.

该实体指示您应该将 aberration_correction_double 列用于 WHERE 子句，而不是 aberration_correction 列（后者似乎只存储 0 或 1，可能表示布尔值）

问题 2.

屏幕截图在双精度中显示 s，您会期望一个句点。例如 而不是预期的.SQLite 将 ，如果要求 REAL（double），则返回 0（因此 0 定位所有）。,-0,20-0.20

不知道是如何进入数据的，但这就是问题所在。解决方法是使用：-,

@Query("SELECT id,firm,model,aberration_correction_double FROM PERSON" + " WHERE round(CAST(replace(aberration_correction_double,',','') AS REAL),2) = round(CAST(replace(:sphecorneastr,',','') AS REAL),2);"

• 可能过头了，因为您可能只需要第一次更换。

你不妨考虑更新基础数据，例如一次性更新：

@Query("UPDATE PERSON SET aberration_correction_double = CAST(replace(aberration_correction_double,',','') AS REAL), aberration_correction = CAST(replace(aberration_correction_double,',','') AS TEXT) WHERE instr(aberration_correction_double,',');"

• 这假定两列应包含相同的值。