mssql和左联接重复记录 [英] mssql and left join duplicate records

问题描述

原始查询

SELECT DISTINCT
      IP.op_code as ip_op_code,
      IPH.op_code as iph_op_code,
      debt_trans.tx_amount as cash,
      DT.tx_amount as revenue
    FROM debt_trans
    LEFT JOIN debt_trans DT ON DT.debt_code=debt_trans.debt_code
    LEFT JOIN instplan IP ON IP.debt_code=debt_trans.debt_code
    LEFT JOIN instplanheader IPH ON IPH.debt_code=debt_trans.debt_code

    AND debt_trans.tran_code NOT IN ('DR3001','DR3002','DR3003','DR3004','RP1800','CC5000')
    AND debt_trans.tx_amount > 0
    AND debt_trans.tx_date >= '2019-02-04' AND debt_trans.tx_date <= '2019-02-04'

    AND IP.ipactualpaymentamt > 0
    AND IP.tran_code NOT IN ('DR3001','DR3002','DR3003','DR3004','RP1800','CC5000')
    AND IP.ipactualpaymentdt >= '2019-02-04' AND IP.ipactualpaymentdt <= '2019-02-04'
    AND (IP.ipactualpaymentdt+debt_trans.tx_time)=(debt_trans.tx_date+debt_trans.tx_time)

    AND DT.tran_code IN ('CC5000')
    AND DT.tx_amount > 0.00
    AND DT.tx_date >= '2019-02-04' AND DT.tx_date <= '2019-02-04'

    AND DT.tx_date=debt_trans.tx_date
    AND DT.tx_time=debt_trans.tx_time

    AND IPH.ipplanid=IP.ipplanid

输出结果

Row Count : 4

[0] => Array
    (
        [ip_op_code] => DOMP
        [iph_op_code] => DOMP
        [cash] => 5.00
        [revenue] => 2.25
    )

[1] => Array
    (
        [ip_op_code] => DOMP
        [iph_op_code] => DOMP
        [cash] => 671.00
        [revenue] => 301.95
    )

[2] => Array
    (
        [ip_op_code] => RHYSL
        [iph_op_code] => RHYSL
        [cash] => 5.00
        [revenue] => 2.25
    )

[3] => Array
    (
        [ip_op_code] => RHYSL
        [iph_op_code] => RHYSL
        [cash] => 671.00
        [revenue] => 301.95
    )

预期结果

行数:2

[0] => Array
    (
        [ip_op_code] => DOMP
        [iph_op_code] => DOMP
        [cash] => 5.00
        [revenue] => 2.25
    )

[1] => Array
    (
        [ip_op_code] => RHYSL
        [iph_op_code] => RHYSL
        [cash] => 671.00
        [revenue] => 301.95
    )

我现在已经添加了完整的正确查询，我正在尝试做，请您尝试帮我解决这个问题.

i have now added the full proper query im trying to do please could you try help me on this one.

我有3张桌子.

debt_trans 计划 instplanheader

debt_trans instplan instplanheader

并且我必须再次加入btl_trans，因为我必须获得下一行.

and i have to join debt_trans in again as i have to get the next row.

当我加入instplan和instplanheader时，一切似乎都准备就绪了

it all seems to go to pot when i join instplan and instplanheader

推荐答案

这对我来说似乎是荒谬的:

This seems a touch nonsensical to me:

        AND i.ipactualpaymentdt >= '2019-02-04' 
        AND i.ipactualpaymentdt <= '2019-02-04'

这篇关于mssql和左联接重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！