android - 是否存在根据症状返回疾病类型的查询

我正在使用将显示疾病的android health应用程序。因为我有三个表，例如症状，疾病和symptom_disease表，它们将链接这两个表。用户输入诸如头晕，晕厥，乏力等症状时，将显示出高血压疾病。我如何为此编写查询来区分这个东西？以及如何实施

最佳答案

这个简单的例子：

这些表：

create table if not exists symptoms (id integer primary key, symptom text unique);
create table if not exists diseases (id integer primary key, disease text unique);
create table if not exists symptom_disease (diseaseid integer not null, symptomid integer not null, primary key(symptomid,diseaseid));

将此数据放入表中：

insert into diseases (disease) values ('cold'),('flu'),('hypertensive disease'),('vertigo');
insert into symptoms (symptom) values('dizziness'),('syncope'),('asthenia'),('headache'),('coughing'),('sorethroat'),('fever');
insert into symptom_disease values (3,1),(3,2),(3,3),(1,4),(2,4),(1,5),(2,5),(1,6),(2,6),(2,7),(4,1);

这会获取以哮喘为首发的头晕，晕厥或症状的疾病数据：-

select disease, count() AS rating
from diseases
    join symptom_disease on diseases.id = diseaseid
    join symptoms on symptomid = symptoms.id
where symptom = 'dizziness' or symptom = 'syncope' or symptom like 'asth%'
group by disease
order by rating desc
;

它得到

hypertensive disease    3
vertigo                 1

最有可能的高血压疾病，因为有3种症状

您可以进行外键操作，从而使symptom_disease表不会有不良数据

create table if not exists symptom_disease (
    diseaseid integer not null references diseases(id) on delete cascade on update cascade ,
    symptomid integer not null references symptoms(id) on delete cascade on update cascade,
    primary key(symptomid,diseaseid));

您需要打开外键才能使用外键。