android - 有没有根据症状返回疾病类型的查询

Question

我正在使用一个会显示疾病的安卓健康应用程序。因为我有三个表，例如症状、疾病和将链接这两个表的 symptom_disease 表。用户输入头晕、晕厥、乏力等症状，就会显示高血压病。我如何编写查询来区分这个东西？以及如何实现。

Answer 1

这个如何完成的简单示例:-

这些表:-

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));

使用外键需要开启外键。