不能在一个数据库中的两个表之间使用'where‘进行查询吗？

Question

今天，当我在Android上学习房间的时候，我遇到了一个问题。

一切都很好，但我就是不能查询两个表之间的位置。

以下是我的代码：

..........................................................................................................................

这段代码运行良好

@Query("select users.id,users.name from users where score > :score and assist > :assist")

List<UserSimple> getUserWithLimits(int score,int assist);

但是下面的代码什么也得不到：

@Query("select users.id,users.name from users,performs where users.id = performs.id and performs.score > :score and performs.assist > :assist")

List<UserSimple> getUserWithLimits(int score,int assist);

下面是我使用Room：..............................................................................................................创建的表.用户表：

 @Entity(tableName = "users", 
        primaryKeys = {"id", "name"},
        indices = {
                @Index(value = "id", unique = true)
        })
public class User {
    @android.support.annotation.NonNull
    @ColumnInfo(name = "id")
    private String id;

    @android.support.annotation.NonNull
    @ColumnInfo(name = "name")
    private String name;

    @ColumnInfo(name = "position")
    private String position;

    @Embedded
    private UserPerforms performs;

    @Ignore
    public User() {
    }

    public User(String id, String name, String position, UserPerforms performs) {
        this.id = id;
        this.name = name;
        this.position = position;
        this.performs = performs;
    }

    getters/setters/toString()...
}

..........................................................................................................................执行表：

    @Entity(tableName = "performs",
        primaryKeys = "p_id",
        foreignKeys = @ForeignKey(entity = User.class
                , parentColumns = "id"
                , childColumns = "p_id")) //定义主键
public class UserPerforms {
    @android.support.annotation.NonNull //
    @ColumnInfo(name = "p_id")
    private String p_id;

    @ColumnInfo(name = "score")
    private int score;

    @ColumnInfo(name = "assist")
    private int assist;

    @Ignore
    public UserPerforms() {
    }

    public UserPerforms(String p_id, int score, int assist) {
        this.p_id = p_id;
        this.score = score;
        this.assist = assist;
    }

    ...getters/setters/toString()..

}

..........................................................................................................................userSimple类：

public class UserSimple {
    @ColumnInfo(name = "id")
    private String id;

    @ColumnInfo(name = "name")
    private String name;

    ...getters/setters/toString()

    public UserSimple(String id, String name) {
        this.id = id;
        this.name = name;
    }
}

有人能帮我吗?提前谢谢。

Answer 1

您加入了错误的列。performs表中的id列是p_id，而不是id。试试这个：

@Query("select users.id,users.name from users,performs where users.id = performs.p_id and performs.score > :score and performs.assist > :assist")