SQL内联和过滤掉结果[英] SQL Inner Join and filtering out results

本文是小编为大家收集整理的关于SQL内联和过滤掉结果的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在为报告进行查询,我需要帮助我一直在尝试工作数周的内在连接.它是一个较大的报告查询,但我在最后一部分中需要帮助,应该过滤某些结果.

cwo_work_order_line_item中的每一行代表通过事务序列分组在一起的某个工作顺序上的项目.此查询正在加入此表和另一个表,以获取报告,cat_cust_item_acct_activity.该表显示了与某个帐户有关的不同交易.

该报告的某些部分应该仅显示被指控为"估算"的项目.此查询非常好,直到项目更改为与设置时不同交易的实际价格.

.

问题是表cwo_work_order_line_item以不同的事务序列(trans_seq列)将新项目插入其中,并且cat_cust_item_acct_activity也获得了一个新项目.发生这种情况,而不是实际修改代表每个表的原始行.

这意味着即使价格从估算更改为实际.

,报告仍在报告上显示原始项目(设置项目).

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

http://i.imgur.com/sdcvg6v.png

在图片中的示例中,第一表是cwo_work_order_line_item. trans_seq 1052833是设置项目时的三幅度.查询仅检查这些项目是否设置为"实际"而不是"估计"的价格,该价格在Price_status_enum列中表示.

第三行显示了不同的交易,即1052834,客户将商品价格更改为实际价格,因此"实际".

在第二个表中,cat_cust_item_acct_activity,您可以看到客户完成的操作.从第一表和第二个表中似乎连接项目的唯一一件事是rtrans_lineitm_seq,而在第二个表中是cust_item_acct_detail_item_nbr.

所以这就是我认为我需要做的: 我需要进行此查询以检查第二个表,以查看item_acct_activity_code中的" modify_price"是否存在,然后检索该行的cust_item_acct_detail_item_nbr.然后确保在cust_item_acct_detail_item_nbr等于第一个表中的rtrans_lineitm_seq等于第一个表中的第一个表中的行

原始查询: 如果Price_status_enum在设置时没有在同一交易中更改或更改.

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

推荐答案

尝试使用子选择以获取最新序列编号,如下:

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'

本文地址:https://www.itbaoku.cn/post/597363.html

问题描述

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.

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.

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

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.

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

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.

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

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'