查询选择唯一的ROWS [英] Query for Selecting Unique ROWS

问题描述

尊重的SIR / MADAM

我有3个像t1的表，其中包含3个字段（Id，Name，registrationdate）和第二个t2（Id，投诉，投诉）和第三个t3（Id，Disease， diseasedate）。我注册了一个像1的id，我插入了2个投诉和2个疾病。现在我想要检索投诉和疾病的数据。我试过这样的

Respected SIR/MADAM
I have 3 Tables like t1 which contains 3 field (Id,Name,registrationdate) and second t2(Id,Complaints,complaindate) and third t3(Id,Disease,diseasedate). I am register a id like 1 and i have inserted 2 complaints and 2 diseases . now i want to retrieve the data of complaints and diseases. i have tried like this

select distinct a.id,b.id,c.id,b.complaints,c.Disease,a.registrationdate,b.complaindate,c.diseasedate from t1 a left join t2 b on a.id = b.id left join t3 c on a.id = c.id and b.id= c.id ;

但它显示4条记录

类似

1,1,1，Complaint1，登革热，2014-06-23

1,1,1，Complaint2，登革热，2014-06-23

1,1,1，投诉1，发烧，2014-06-23

1,1,1，投诉2，发烧，2014-06-23



但是我只想要这样的2条记录

1 ，1,1，投诉1，登革热，2014-06-23

1,1,1，投诉2 ，发烧，2014-06-23



我怎么能得到它。

你的帮助对我来说是合适的..

先谢谢

but it shows 4 records
like
1, 1, 1, Complaint1, Dengue,2014-06-23
1, 1, 1, Complaint2, Dengue,2014-06-23
1, 1, 1, Complaint1, fever,2014-06-23
1, 1, 1, Complaint2, fever,2014-06-23

but i want only 2 records like this
1, 1, 1, Complaint1, Dengue,2014-06-23
1, 1, 1, Complaint2, fever,2014-06-23

how can i get it.
your help will be appriciatable for me ..
Thanks In Advance

推荐答案

Nawaz，



我假设你已经创建了表格t3与t2分开，因为每个投诉可能有许多与之相关的死亡。如果是，您需要在投诉表（t2）中使用PK，并在死亡表（t3）中使用引用t2的FK来模拟死亡与相应投诉之间的关联。一旦数据模型正确完成，您可以重新编写SQL。

Nawaz,

I assume you have have created table t3 separate from t2 as each complaint can have many deceases associated with it. If yes, you need a PK in Complaint table(t2) and an FK in decease table(t3) referencing t2 that models the association between decease and corresponding complaint. You may re-write you SQL once the data model is done right.

您好，



您必须更改表格结构很少。在T2中添加一列作为ID_New，这将保持表T2的唯一Id。并在T3中使用T2新id作为参考。所以，你的查询可能如下所示

Hi,

You have to change your table structure little. Add a column as ID_New in T2 which will maintain the unique Id of table T2. And use that T2 new id in T3 as reference. So, your query might look like below

select distinct
a.id,
a.regdate,
b.id,
b.complain,
b.compdate,
c.id,
c.disease,
c.diseasedate
from T1 as a
left join  T2 as b on a.id = b.id
left join  T3 as c on b.id_new= c.id

希望这会对你有帮助。

Hope this will help you.

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