从组合SQL中的多个值中选择一个值 [英] Pick one value from multiple value from the combination sql

问题描述

我们的id表如下

id  |  newsecid
--- |  ---
1   |  10
2   |  20
3   |  30

单个id将具有一个newsecid

其他桌子很壮

id  |  featid
--- |  ---
1   |  5
1   |  6
2   |  2
2   |  4

一个ID可以有多个专长ID

参考表

newsecid  |  featid  |  oldsecid
---       |  ---     |  ---
6         |  null    |  2
2         |  null    |  6
3         |  null    |  5  
1         |  NULL    |  1
1         |  5       |  4
16        |  NULL    |  16
16        |  4       |  13
25        |  NULL    |  26
25        |  6       |  25
26        |  NULL    |  26
26        |  6       |  24

当具有相同id的多个要素时，我们将其视为null以便与引用表联接

对于所有新的secidid，不需要从引用表中获取oldsecid的newsecid和featid的组合，因为总是只有一个值，例如在newsecids为6,2和3的情况下，featid为null.

但是对于仅newsecids 1,16,25,26，我们必须从ref表的newsecid和featid的组合中选择oldsecid，因为它有2个值，一个为空featid，另一个为一些featid值. /p>

不需要我使用的组合的情况

select c.oldsecid from id i
inner join feat f on i.id=f.id
inner join ref c  on i.newsecid = c.newsecid

由于只有一个值，因此我将从ref表中获取oldsecid 2、6、5.

对于使用上述查询的1,16,25,26情况，我得到的是随机的oldsecid.在这种情况下，我需要featid不为null的oldsecid.

我们可以将newsecid的条件硬编码为1,16,25,26，因为我只有这些情况.

任何帮助

解决方案

根据我的理解，请尝试:

select c.oldsecid from id i
inner join feat f on i.id=f.id
inner join ref c  on i.newsecid = c.newsecid
Inner join( select newSecId,count(*)  as newSecIdCount from ref group by newSecId) r on r.newSecId=i.newSecId

Where (r.newSecIdCount=1 or c.feetid is not null)

we have id table as following

id  |  newsecid
--- |  ---
1   |  10
2   |  20
3   |  30

Single id will have single newsecid

other table is feat

id  |  featid
--- |  ---
1   |  5
1   |  6
2   |  2
2   |  4

One id can have multiple feat ids

ref table

newsecid  |  featid  |  oldsecid
---       |  ---     |  ---
6         |  null    |  2
2         |  null    |  6
3         |  null    |  5  
1         |  NULL    |  1
1         |  5       |  4
16        |  NULL    |  16
16        |  4       |  13
25        |  NULL    |  26
25        |  6       |  25
26        |  NULL    |  26
26        |  6       |  24

When there are multiple featids for the same id the we consider them as null to join with the ref tables

For all the newsecids there is no need for the combination of newsecid and featid to get the oldsecid from the ref table because there is always only one value like in the case of newsecids as 6,2 and 3 with featid as null.

But for only newsecids 1,16,25,26 we have to pick oldsecid from the combination of newsecid and featid from the ref table as there are 2 values for that.One with null featid and one with some featid value.

The cases where there is no requirement for the combination I am using

select c.oldsecid from id i
inner join feat f on i.id=f.id
inner join ref c  on i.newsecid = c.newsecid

Using this I am getting oldsecid 2,6,5 from the ref table since there is only one value.

For the cases 1,16,25,26 using the above query I am getting random oldsecid .In this I need that oldsecid where featid is not null.

We can hardcode the condition of newsecid for 1,16,25,26 as I dont have only these cases only.

Any help

解决方案

as per my understanding , Try:

select c.oldsecid from id i
inner join feat f on i.id=f.id
inner join ref c  on i.newsecid = c.newsecid
Inner join( select newSecId,count(*)  as newSecIdCount from ref group by newSecId) r on r.newSecId=i.newSecId

Where (r.newSecIdCount=1 or c.feetid is not null)

这篇关于从组合SQL中的多个值中选择一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！