SQL Inner加入和过滤结果 [英] SQL Inner Join and filtering out results

问题描述

我正在查询报表，我需要帮助INNER JOINs我一直在努力工作几个星期。

I'm doing a query for a report, and I need help with the INNER JOINs I've been trying to make work for weeks. Its apart of a larger report query, but I need help in this final part taht is supposed to filter certain results.

cwo_work_order_line_item 中的每一行， / code>表示按事务序列分组在一起的某个工单的项目。此查询正在加入此报表的另一个表， cat_cust_item_acct_activity 。该表显示了处理某个帐户的不同交易。

Each row in cwo_work_order_line_item represents a item on a certain work order grouped together by transaction sequences. This query is joining this and another table for a report, cat_cust_item_acct_activity. That table shows different transactions dealing with a certain account.

报告的这一部分应该只显示收费为估计的项目。

This certain section of the report is supposed to only show items that are charged as 'Estimate.' This query works perfectly fine until an item gets changed to an actual price on a different transaction than when it was set up.

问题是表 cwo_work_order_line_item

The problem is that the table cwo_work_order_line_item gets a new item inserted into it with a different transaction sequence (trans_seq column) and cat_cust_item_acct_activity gets a new item as well. This happens instead of actually modifying the original row representing the item ean each table.

这意味着报告仍然显示原始项目即使价格已从估计更改为实际。

What this means is that the report is still showing the original item (the setup item) on the report even after the price was changed from Estimate to Actual.

这是我的数据库中的两个表的图片。看到这将帮助我更好地解释问题

Here's a pic of the two tables in my database. Seeing this will help me better explain the problem

http ：//i.imgur.com/SdcVG6V.png

在图片的示例中，第一个表是 cwo_work_order_line_item 。 trans_seq 1052833是项目设置时的过渡。查询仅检查这些项目是否设置为ACTUAL而不是ESTIMATE的价格，这在price_status_enum列中表示。

In the example in the picture, the first table is cwo_work_order_line_item. the trans_seq 1052833 is the tranasction when the items were set up. The query is only checking if these items are set to a price of 'ACTUAL' and not 'ESTIMATE', which is represented in price_status_enum column.

第三行显示了一个不同的交易，1052834，客户将商品价格更改为实际价格，因此为实际。

The 3rd row shows a different transaction, 1052834, where the customer changed the item price to an actual price, hence "ACTUAL."

在第二个表中， cat_cust_item_acct_activity ，您可以看到客户完成的操作。看起来连接第一和第二个表中的项目的唯一事情是第一个表中的 rtrans_lineitm_seq ，以及 cust_item_acct_detail_item_nbr 第二。

In the second table, cat_cust_item_acct_activity, you can see the actions completed by the customer. The only thing that seemingly connects the items from the first and second table is rtrans_lineitm_seq from the first table, and cust_item_acct_detail_item_nbr in the second.

所以这是我想我需要做的：
我需要使这个查询检查第二个表，看看是否有一个'MODIFY_PRICE' item_acct_activity_code ，然后检索该行的 cust_item_acct_detail_item_nbr 。然后确保第一个表中的行未加入，其中 cust_item_acct_detail_item_nbr 等于第一个表中的 rtrans_lineitm_seq 表

So this is what I think I need to do: I need to make this query to check the second table to see if there is a 'MODIFY_PRICE' in item_acct_activity_code and then retrieve that row's cust_item_acct_detail_item_nbr. Then make sure that the row in the first table is not joined where the cust_item_acct_detail_item_nbr is equal to the rtrans_lineitm_seq in the first table

原始查询：
如果在设置时在同一事务中price_status_enum未更改或更改，此查询将起作用。

Original Query: This query works if the price_status_enum is not changed or changed in the same transaction when it is set up.

SELECT
   DISTINCT(ca.cust_acct_id)                 
FROM
   cwo_work_order_acct ca              
INNER JOIN
   cat_cust_item_acct_activity acty                
      ON                      ca.organization_id = acty.organization_id                     
      AND ca.cust_acct_id = acty.cust_acct_id                     
      AND ca.cust_acct_code = acty.cust_acct_code              
INNER JOIN
   cwo_work_order_line_item cli                         
      ON                          acty.organization_id = cli.organization_id                         
      AND acty.wkstn_id = cli.wkstn_id                         
      AND acty.trans_seq = cli.trans_seq                         
      AND acty.business_date = cli.business_date                         
      AND acty.rtl_loc_id = cli.rtl_loc_id                         
      AND acty.rtrans_lineitm_seq = cli.rtrans_lineitm_seq              
      AND cli.price_status_enum ='ESTIMATE

推荐答案

尝试使用子选择为了获取最新的序列号，如下所示：

Try using a sub select in order to get the most recent sequence number, like below:

SELECT DISTINCT(ca.cust_acct_id)                 
FROM cwo_work_order_acct ca              
INNER JOIN
(
SELECT 
    act.organization_id,
    act.wkstn_id,
    act.business_date,
    act.rtl_loc_id,
    act.rtrans_lineitm_seq,
    max(act.trans_seq) trans_seq --Get the most recent seq number
  FROM cat_cust_item_acct_activity act                
  GROUP BY
        act.organization_id,
        act.wkstn_id,
        act.business_date,
        act.rtl_loc_id,
        act.rtrans_lineitm_seq        
  ) as acty
  ON  ca.organization_id = acty.organization_id                     
  AND ca.cust_acct_id = acty.cust_acct_id                     
  AND ca.cust_acct_code = acty.cust_acct_code

INNER JOIN cwo_work_order_line_item cli                         
  ON  acty.organization_id = cli.organization_id                         
  AND acty.wkstn_id = cli.wkstn_id                         
  AND acty.trans_seq = cli.trans_seq                         
  AND acty.business_date = cli.business_date                         
  AND acty.rtl_loc_id = cli.rtl_loc_id                         
  AND acty.rtrans_lineitm_seq = cli.rtrans_lineitm_seq              
  AND cli.price_status_enum ='ESTIMATE'

这篇关于SQL Inner加入和过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！