sql—如何计算插入到窗体中的数据数

3pmvbmvn  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(303)

我正在做一个项目,我需要确定插入数据库的学生是否有冠状病毒,并且我将症状作为复选框返回数字1如果选中,我如何使最后一列(状态)在条目上计数为1并返回状态,无论是阴性还是阳性?
结果是这样的

[ID , Student , symptoms1 , symptom2 , symptom3 ,Status]
[1234 Scott   , 1.        , 1.       , 1.       , positive]

我尝试了这个,但仍然导致右括号出错

create table covid(ID NUMBER(7) , NAME VARCHAR2(32) ,DEPT VARCHAR2(16) , FEVER NUMBER(1) , 
COUGH NUMBER(1) ,TIREDNESS NUMBER(1) ,SHORT_BREATH NUMBER(1) ,SORE_THROAT NUMBER(1) ,
CHEST_PAIN NUMBER(1) ,LOSE_SENSES NUMBER(1),RUNNY_NOSE NUMBER(1), TEST_DATE DATE ,
SYMPTOMS NUMBER(16) ,
CASE
WHEN SYMPTOMS>= 3 THEN 'POSITIVE' ELSE 'NEGATIVE'
END AS RESULT);
iovurdzv

iovurdzv1#

一种方法,但是你没有说如何基于症状的不同组合来评估状态,你需要把你的逻辑要求放在任何地方

select id , student ,
case when symptoms1 = 1 and symptom2 = 1 and symptom3 =1 then 'POSITIVE' 
     when symptoms1 = 1 and symptom2 = 1 and symptom3 =0 then 'XXXXXXXX'
     when symptoms1 = 1 and symptom2 = 0 and symptom3 =1 then 'XXXXXXXX'
     when symptoms1 = 1 and symptom2 = 0 and symptom3 =0 then 'XXXXXXXX'
     when symptoms1 = 0 and symptom2 = 1 and symptom3 =1 then 'XXXXXXXX'
     when symptoms1 = 0 and symptom2 = 0 and symptom3 =1 then 'XXXXXXXX'
     when symptoms1 = 0 and symptom2 = 1 and symptom3 =0 then 'XXXXXXXX'
     when symptoms1 = 0 and symptom2 = 0 and symptom3 =0 then 'NEGATIVE'
 end as status
 from table

相关问题