sql查询，根据连接的存在，有条件地从表中选择一行

0aydgbwb 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(410)

我有一个连接3个表的查询，我试图根据第三个表中是否存在记录来检索一组有条件的行。
如果第三个表中有匹配项，那么我只需要第一个表中的匹配记录。如果第三个表中没有匹配项，那么我只需要第一个表中的一条记录。

select
o.Object_ID,
reqCon.Connector_ID,
req.Object_ID as Requirement_ID

from t_object o

left join t_connector reqCon on reqCon.End_Object_ID = o.Object_ID and reqCon.Stereotype = 'deriveReqt'
left join t_object req on reqCon.Start_Object_ID = req.Object_ID and req.Stereotype = 'functionalRequirement'

这将生成以下类型的结果，但以粗体突出显示的是所需的实际行。

Object_ID   Connector_ID    Requirement_ID
40936   43259
40936   43260

**40936 43299   38013**

40943   43264
40943   43265

**40943 43298   38014**
**44088 46245**

44088   46246   

**42669 44655**

42669   44656

**42670 44657**

42670   44658

sql mysql enterprise-architect

来源：https://stackoverflow.com/questions/51455691/sql-query-to-conditionally-select-a-row-from-a-table-depending-on-join-existence

1条答案

按热度按时间

jk9hmnmh1#

一个解决办法是 union all :

select o.Object_ID, reqCon.Connector_ID, req.Object_ID as Requirement_ID
from t_object o join
     t_connector reqCon
     on reqCon.End_Object_ID = o.Object_ID and
        reqCon.Stereotype = 'deriveReqt' join
     t_object req
     on reqCon.Start_Object_ID = req.Object_ID and
        req.Stereotype = 'functionalRequirement'
union all
select o.Object_ID, min(reqCon.Connector_ID), null as Requirement_ID
from t_object o left join
     t_connector reqCon
     on reqCon.End_Object_ID = o.Object_ID and
        reqCon.Stereotype = 'deriveReqt' left join
     t_object req
     on reqCon.Start_Object_ID = req.Object_ID and
        req.Stereotype = 'functionalRequirement'
group by o.Object_Id
having sum(req.Object_Id is not null) = 0;

第一个查询引入匹配项。第二个为每个不匹配的对象引入一行。

赞(0）回复(0）举报 2021-06-21

我来回答

sql查询，根据连接的存在，有条件地从表中选择一行

1条答案

相关问题

热门标签

最新问答