SQL Server Find and set only first duplicated value remaining duplicates set to 0

mqxuamgl 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(116)

Could someone help me to make a query that sets the value to field but only once per duplicated record? I do have some duplicated rows and I need a column that stores the value of my transaction only once. I did tried but without good effect, right now I'm stuck with a query like this.

SELECT         JOURNALNUMBER, SUBLEDGERVOUCHER, ACCOUNTINGCURRENCYAMOUNT, MAINACCOUNTVALUE, 
                    ITEMID,  QTY, INVENTTRANSID, 
                         (SELECT        count(journalnumber)
                           FROM            TestGeneralTransY AS tab
                           WHERE        (TestGeneralTransY.JOURNALNUMBER = JOURNALNUMBER) AND (TestGeneralTransY.ITEMID = ITEMID)
                           AND (TestGeneralTransY.mainaccountvalue = mainaccountvalue) AND (TestGeneralTransY.ACCOUNTINGCURRENCYAMOUNT = ACCOUNTINGCURRENCYAMOUNT)
                           ) AS MInAmount
FROM            TestGeneralTransY

I want that the MinAmount would show only one valu per transaction like in PAF-004644308 I would need one row with value -79,5 and the second with 0. I do need this because I need to do sum calculations and with duplicated values its not possible for me.

JOURNALNUMBER	SUBLEDGERVOUCHER	ACCOUNTINGCURRENCYAMOUNT	MAINACCOUNTVALUE	ITEMID	QTY INVENTTRANSID
PAF-004644305	WZ-0187132	-39.750000	3110001 MPL-WST-0044	-186.000000 PT020719031 1
PAF-004644308	WZ-0187135	-79.500000	3110001 MPL-WST-0036	-260.000000 PT021519260 2
PAF-004644308	WZ-0187135	-79.500000	3110001 MPL-WST-0036	-260.000000 PT021519260 2
PAF-004644324	WZ-0187146	-397.490000 3110001 MPA-PAL-0010	-10.000000	PT022252398 1
PAF-004647078	WZ-0187322	-10573.990000	3110001 MPL-FOR-0227	-4500.000000	PT022149061 1
PAF-004654294	FSL22000020 397.490000	3110001 MPA-PAL-0010	-10.000000	PT022252398 1
PAF-004654296	FSL22000021 79.500000	3110001 MPL-WST-0036	-260.000000 PT021519260 2
PAF-004654296	FSL22000021 79.500000	3110001 MPL-WST-0036	-260.000000 PT021519260 2
PAF-004654297	FSL22000022 39.750000	3110001 MPL-WST-0044	-186.000000 PT020719031 1
PAF-004660123	FSL22000026 10573.990000	3110001 MPL-FOR-0227	-4500.000000	PT022149061 1
PAF-004667259	WZ-0188549	-357.740000 3110001 MPA-PAL-0010	-9.000000	PT022252398 1
PAF-004667260	WZ-0188562	-119.250000 3110001 MPL-WST-0044	-260.000000 PT020719031 3
PAF-004667260	WZ-0188562	-119.250000 3110001 MPL-WST-0044	-260.000000 PT020719031 3
PAF-004667260	WZ-0188562	-119.250000 3110001 MPL-WST-0044	-260.000000 PT020719031 3
PAF-004673442	WZ-0189010	-73.770000	3110001 MPA-PAL-0010	-1.000000	PT022252398 1
PAF-004673524	WZ-0189011	-39.750000	3110001 MPL-WST-0036	-260.000000 PT021519260 1
PAF-004686484	FSL22000103 39.750000	3110001 MPL-WST-0036	-260.000000 PT021519260 1
PAF-004686612	FSL22000104 431.510000	3110001 MPA-PAL-0010	-9.000000	PT022252398 2
PAF-004686612	FSL22000104 431.510000	3110001 MPA-PAL-0010	-1.000000	PT022252398 2
PAF-004686824	FSL22000106 119.250000	3110001 MPL-WST-0044	-260.000000 PT020719031 3
PAF-004686824	FSL22000106 119.250000	3110001 MPL-WST-0044	-260.000000 PT020719031 3
PAF-004686824	FSL22000106 119.250000	3110001 MPL-WST-0044	-260.000000 PT020719031 3

sql-server

来源：https://stackoverflow.com/questions/75854762/find-and-set-only-first-duplicated-value-remaining-duplicates-set-to-0

1条答案

按热度按时间

bnlyeluc1#

You can use ROW_NUMBER() to numerate your items, and then CASE to return ACCOUNTINGCURRENCYAMOUNT only for first one.

select
    JOURNALNUMBER,
    SUBLEDGERVOUCHER,
    case
        when rn = 1 then ACCOUNTINGCURRENCYAMOUNT
        else 0
    end ACCOUNTINGCURRENCYAMOUNT,
    MAINACCOUNTVALUE,
    ITEMID,
    QTY,
    INVENTTRANSID
from
    (
        select
            JOURNALNUMBER,
            SUBLEDGERVOUCHER,
            ACCOUNTINGCURRENCYAMOUNT,
            MAINACCOUNTVALUE,
            ITEMID,
            QTY,
            INVENTTRANSID,
            ROW_NUMBER() OVER (
                PARTITION BY JOURNALNUMBER
                ORDER BY (SELECT NULL)
            ) rn
        from
            TestGeneralTransY
    )

Please check if OVER clause of ROW_NUMBER() makes sense for your data.

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server Find and set only first duplicated value remaining duplicates set to 0

1条答案

相关问题

热门标签

最新问答