按最大日期过滤的Join - concatenation索引的SQL子查询

Question

我对SQL比较陌生--上周才开始使用它。我在通过基于连接的索引将两个表连接在一起时遇到了一些问题(我可以这样做，但它显示了几个日期)，然后按最大日期对其进行过滤

请参阅下面的第一个版本，其中显示了连接在一起的表。我需要连接两个表。INVENTORY_TRANSACTION_HIST2 (作业) inventory_transaction_hist (与项目细节相关的更多详细信息)

这些表之间没有关联，所以我不得不从几个字段构建连接的表：part_no||lot_batch_no||serial_no||location_no用于两个表之间的关联。

order_no||release_no||sequence_no||line_item_no只与第二个表相关，并创建过滤日期所需的索引

select
to_char(job.dated,'dd/mm/yyyy hh:mm:ss') date_issued,
job.userid,
job.part_no,
job.quantity,
job.lot_batch_no,
job.serial_no,
job.cost,
job.quantity* job.cost total_cost,
job.source note,
hist.dated,
hist.order_no,
hist.release_no,
hist.sequence_no,
hist.line_item_no

from
inventory_transaction_job job
left join inventory_transaction_hist hist
on job.part_no||job.lot_batch_no||job.serial_no||job.location_no = hist.part_no||hist.lot_batch_no||hist.serial_no||hist.location_no
where (job.source like UPPER('%'||'&Job_No'||'%') or job.source like LOWER('%'||'&Job_No'||'%'))
and hist.transaction LIKE '%INM-IN%'
and hist.direction LIKE '+'
order by job.part_no

我需要根据历史上最近的日期进一步拆分。表(因为一些批次/序列号只显示为'*‘，所以可以显示每个批次/序列号的几个订单号-我只想显示这些订单的最新订单)

我已经尝试了下面和一些其他的变体，但我似乎不能让它工作。

select
to_char(job.dated,'dd/mm/yyyy hh:mm:ss') date_issued,
job.userid user_id,
job.part_no,
job.quantity,
job.lot_batch_no,
job.serial_no,
job.cost,
job.quantity* job.cost total_cost,
job.source note,
hist.dated,
hist.order_no,
hist.release_no,
hist.sequence_no,
hist.line_item_no

from
inventory_transaction_job job
left join inventory_transaction_hist hist
on job.part_no||job.lot_batch_no||job.serial_no||job.location_no = hist.part_no||hist.lot_batch_no||hist.serial_no||hist.location_no
where (job.source like UPPER('%'||'&Job_No'||'%') or job.source like LOWER('%'||'&Job_No'||'%'))
and hist.transaction LIKE '%INM-IN%'
and hist.direction LIKE '+'
and job.order_no||job.sequence_no||job.line_item_no IN
(SELECT
hist2.order_no||hist2.sequence_no||hist2.line_item_no
FROM inventory_transaction_hist hist2
WHERE job.part_no||job.lot_batch_no||job.serial_no||job.location_no LIKE hist2.part_no||hist2.lot_batch_no||hist2.serial_no||hist2.location_no
ORDER BY hist2.Dated DESC LIMIT 1)

任何帮助都将不胜感激。

Answer 1

要理解查询中的逻辑有点困难。。。但是欢迎来到StackOverflow。您应该使用您正在使用的数据库来标记您的问题，我假设该数据库是Oracle。

首先，正如在注释中提到的，您应该在通过"ands“连接的单个列上进行连接，而不是试图将它们连接在一起。例如，这种连接很聪明，但它使查询优化器更难完成它的工作。

我的想法是，您希望从历史表中获得不同组的最新日期。我使用解析函数计算最大日期，然后将其用于比较。

最后的查询如下所示：

 select to_char(job.dated,'dd/mm/yyyy hh:mm:ss') date_issued, job.userid user_id, job.part_no,
        job.quantity, job.lot_batch_no, job.serial_no, job.cost, job.quantity* job.cost total_cost,
        job.source note, hist.dated,  hist.order_no, hist.release_no, hist.sequence_no, hist.line_item_no
from inventory_transaction_job job left join
     (select hist.*,
             MAX(dated) over (PARTITION by hist2.part_no, hist2.lot_batch_no, hist2.serial_no, hist2.location_no) as maxdated
      from inventory_transaction_hist hist
     ) hist
     on job.part_no = hist.part_no and
        job.lot_batch_no = hist.lot_batch_no and
        job.serial_no = hist.serial_no and
        job.location_no = hist.location_no join
     (SELECT hist2.order_no, hist2.lot_batch_no, hist2.serial_no, hist2.location_no, hist2.sequence_no, hist2.line_item_no
      FROM inventory_transaction_hist hist2
      ORDER BY hist2.Dated DESC
      LIMIT 1
     )
where (job.source like UPPER('%'||'&Job_No'||'%') or job.source like LOWER('%'||'&Job_No'||'%')) and
      hist.transaction LIKE '%INM-IN%' and
      hist.direction LIKE '+' and
      hist.dated = hist.maxdated 

不过，我不是百分之百确定，这就是你需要的。不过，也许它会对你有所帮助。

Answer 2

非常感谢你的帮助-真的很感激！管理与一些调整，使其工作，如我所愿。如果您注意到任何可以更改以提高搜索性能的内容，请让我知道！

select
to_char(job.dated,'dd/mm/yyyy hh:mm:ss') date_issued,
job.userid user_id,
job.part_no,
job.quantity,
job.lot_batch_no,
job.serial_no,
job.cost,
job.quantity* job.cost total_cost,
job.source note,
to_char(hist.dated,'dd/mm/yyyy hh:mm:ss') date_PO_received,
hist.order_no,
hist.release_no,
hist.sequence_no,
hist.line_item_no

from
INVENTORY_TRANSACTION_job job
left join (select hist.*, MAX(hist.dated) over (PARTITION by hist.transaction, hist.direction, hist.part_no, hist.serial_no, hist.lot_batch_no order by hist.dated desc) as maxdated
from inventory_transaction_hist hist
) hist
on
job.part_no = hist.part_no and
job.lot_batch_no = hist.lot_batch_no and
job.serial_no = hist.serial_no and
hist.dated = hist.maxdated
where (job.source like UPPER('%'||'&Job_No'||'%') or job.source like LOWER('%'||'&Job_No'||'%'))
and hist.transaction LIKE '%INM-IN%'
and hist.direction = '+'

再次感谢杰米