查询选定的项目列表 [英] Query for selected list of items

问题描述

我正在使用ASP.NET和sqlserver.该项目包括三层，即UI，业务和数据访问层.在UI层中，我选择了具有多个值的列表框.我会将这个值带到数据访问层.

该数据库由疾病和症状组成.就像:

Hi,

I am working with ASP.NET and sqlserver. The project consists of three layers namely UI,business and data access layer. In the UI layer i have listbox with multiple values get selected . i will take this values to the data access layer.

The database consists of diseases and symptoms. just like:

disease    symptom

malaria    fever
malaria    cold
typhoid    fever
typhoid    cough

如果我选择发烧和感冒的症状，则应该返回疟疾.
如果我选择发烧和咳嗽的症状，应该返回伤寒.
如果我仅选择发烧，则应该引起疟疾和伤寒.

If I select the symptoms as fever and cold, it should return malaria.
If I select the symptoms fever and cough, it should return typhoid.
If I select fever only, it should give malaria and typhoid.

Can anyone, please help in writing the query?

推荐答案

Select Distinct disease From ''theTable'' Where symptom in (''fever'',''cough'')

那应该返回带有症状列表的疾病列表.

如果不使用distinct，则将返回同一疾病的多个记录.

Select Distinct disease From ''theTable'' Where symptom in (''fever'',''cough'')

That should return a list of diseases which have the list of symptoms.

If you do not use distinct, you would return multiple records for the same disease.

好吧，首先，当前表结构并不支持所有方案和这种查询结果. br/>
更改为:

Well, first of all your current Table structure does not support all scenarios and this kind of query results.

Change it to:

MstDiseases
DID    Name
1     Maleria
2     Typhoid


MstSymptoms
SID    Name
1     Fever
2     Cold
3     Cough

TrnDiseaseSymptom
TID   DID   SID
1      1     1
2      1     2
3      2     1
4      2     3

现在，我想尝试一下SQL查询.现在应该更简单了.尝试！
提示:使用TrnDiseaseSymptom表获取疾病ID ...

Now, I would like to try the SQL query yourself. It should be much simpler now. Try!
Hint: Use TrnDiseaseSymptom table to get the disease ID''s...

这篇关于查询选定的项目列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！