将具有1到N关系的表合并为1条记录，其中N条记录的最后一个值 [英] combine tables with 1 to N relationship into 1 line of record with the last value of the N record

问题描述

我需要修改我以前关于以下内容的帖子 如何将具有多对多关系的表合并为一条记录

I need a modification of my previous post regarding how to combine tables with 1 to many relationship into 1 line of record

如何将具有一对多关系的表合并到一条记录中

现在我的问题是我的记录现在与一对多关系.我需要显示的是仅最后一条记录，并将其合并为一行 表tbl_equipment和tbl_warranty

now my problem is my record has now 1 to many relationship. What I need to show is the last record only and combine it in a single line tables tbl_equipment and tbl_warranty

这是所需的输出

这是我要实现的代码

 SELECT
 a.equipmentid,
 a.codename,
 a.name,
 a.labelid,
 a.ACQUISITIONDATE,
 a.description,
 a.partofid,
 w1.warrantyid as serviceidwarranty,
 w1.startdate,
 w1.enddate,
 w2.warrantyid as productidwarranty,
 w2.startdate,
 w2.enddate,
 s.equipstatusid,
 l.equiplocationid FROM TBL_EQUIPMENTMST a
 left JOIN tbl_equipwarranty w1
 ON w1.equipmentid=a.equipmentid and w1.serviceproduct = 'service'
 left JOIN tbl_equipwarranty w2
 ON w2.equipmentid=a.equipmentid and w2.serviceproduct = 'product'
 left join tbl_equipstatus s
 on a.equipmentid = s.equipmentid
 left join tbl_equiplocation l
 on a.equipmentid = l.equipmentid  WHERE a.equipmentid = '112'

我只想在输出中显示1条记录，其中包含保修产品和保修服务的最后价值.谁能指导我如何修改我的代码，以便当我尝试连接上面列出的所有表时，只能产生1条记录，最后一条保修记录作为输出. 我正在使用firebird作为数据库.如果您在mysql中有解决方案，请告诉我，请病态尝试在firebird中找到对应的对象.

I only want to show 1 record with the last value of warranty product and warranty service in the output. Can anyone guide me how to modify my code so that when I try join all the tables listed above can produce 1 record only with the last record of warranty as an output. I am using firebird as a database. If you have a solution in mysql kindly tell me and ill try to find the counterpart in firebird.

推荐答案

.我想通了子查询可以解决我的问题.子查询对我来说是个新词.我研究了如何使用子查询，并在下面提出了一个解决方案.如果我的代码错误或如何提高查询性能，您可以纠正我

after reading the comment of Barmar at the question for solution. I Figured out subquery can solve my problem. Subquery is a new word for me. I research on how to use subquery and came out with a solution below. you can correct me if my code is wrong or how to improve the performance of the query

SELECT 
    a.equipmentid,a.codename,a.name,a.labelid,a.ACQUISITIONDATE,a.description,a.partofid,
    w1.warrantyid as serviceidwarranty,w1.startdate,w1.enddate,
    w2.warrantyid as productidwarranty,w2.startdate,w2.enddate,
    s.equipstatusid,
    l.equiplocationid 
FROM 
    TBL_EQUIPMENTMST a
left JOIN
    (select first 1 *
     from tbl_equipwarranty 
     where equipmentid='112' and serviceproduct = 'service'
     order by warrantyid desc) w1 ON w1.equipmentid = a.equipmentid 
                                     and w1.serviceproduct = 'service'
left JOIN
    (select first 1 *
     from tbl_equipwarranty 
     where equipmentid = '112' and serviceproduct = 'product'
     order by warrantyid desc) w2 ON w2.equipmentid = a.equipmentid 
                                     and w2.serviceproduct = 'product'
left join
    (select first 1 *
     from tbl_equipstatus 
     where equipmentid = '112'
     order by equipstatusid desc) s on a.equipmentid = s.equipmentid
left join
    (select first 1 *
     from tbl_equiplocation 
     where equipmentid = '112'
     order by equiplocationid desc) l on a.equipmentid = l.equipmentid  
WHERE 
     a.equipmentid = '112'

这篇关于将具有1到N关系的表合并为1条记录，其中N条记录的最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！