-
将字段hsl 金额求和后取绝对值 abs( SUM( a~hsl ) ) AS yfkje
WITH +data AS ( SELECT DISTINCT zbelnr,gjahr,docln FROM @lt_docln AS a )
SELECT b~zbelnr,b~gjahr,abs( SUM( a~hsl ) ) AS yfkje
FROM acdoca AS a
JOIN +data AS b
ON a~rebzg = b~zbelnr
AND a~rebzj = b~gjahr
AND a~rebzz = b~docln
WHERE a~ktosl = 'KBS'
AND a~rldnr = '0L'
AND a~budat LE @gv_keydat
GROUP BY b~zbelnr,b~gjahr
INTO TABLE @DATA(lt_yfkje2).
IF sy-subrc = 0.
* 排序
SORT lt_yfkje2 BY zbelnr gjahr.
ENDIF.
-
将两个字段拼接一起作为条件
方法一:查询时就将两字段拼接,但两字段必须同时为char类型
SELECT ( belnr && gjahr ) AS tdname
FROM rbkp
INTO TABLE @DATA(LT_TMP).
方法二: INNER JOIN 时再拼接 ( concat( a~belnr,a~gjahr ) ) = t~tdname
WITH +data AS ( SELECT DISTINCT gjahr,belnr FROM @lt_tmp AS zda )
SELECT tdobject,tdname,tdid,tdspras
FROM stxh AS t
INNER JOIN +data AS a
ON ( concat( a~belnr,a~gjahr ) ) = t~tdname
AND t~tdobject = 'RBKP'
AND t~tdid = '0001' AND t~tdspras = @sy-langu
INTO TABLE @DATA(lt_htext).
IF sy-subrc = 0.
* 排序
SORT lt_htext BY tdname .
ENDIF.
-
查询时CASE条件求和
当shkzg = 'H' 则金额shkzg取绝对值后 * -1
当shkzg = 'H' 则金额shkzg取绝对值
WITH +data AS ( SELECT DISTINCT ebeln,ebelp FROM @lt_tmp AS a )
SELECT a~ebeln,a~ebelp,
SUM( CASE WHEN a~shkzg = 'S' THEN abs( a~dmbtr )
WHEN a~shkzg = 'H' THEN abs( a~dmbtr ) * -1
END ) AS dmbtr
FROM ekbe AS a
JOIN +data AS b
ON a~ebeln = b~ebeln
AND a~ebelp = b~ebelp
WHERE a~dmbtr NE 0
AND a~vgabe IN ('2','3')
AND a~budat LE @gv_keydat
GROUP BY a~ebeln,a~ebelp
INTO TABLE @DATA(lt_fpje2).
IF sy-subrc = 0.
* 排序
SORT lt_fpje2 BY ebeln ebelp.
ENDIF.
-
截取前导0进行inner join:ltrim( a~zgxlh, 0 )
SELECT a~zsdfm,a~zgxlh,a~zxszz,a~zxsbg,SUM( c~kavla ) AS kavla
FROM zsdt004 AS a
* INNER JOIN pbvpv AS b ON a~zgxlh = b~vpmat " add 20230925
INNER JOIN mara AS b ON ltrim( a~zgxlh, @zif_bc_cons=>cns_0 ) = b~wrkst " add 20230925
GROUP BY a~zsdfm,a~zgxlh,a~zxszz,a~zxsbg
INTO TABLE @DATA(lt_kcl1).
-
右截取10位进行inner join:right( a~objnr,10 )
SELECT b~rcntr,a~stagr,a~sme001,a~sme002,a~sme003,a~sme004,a~sme005, a~sme006,a~sme007,a~sme008,a~sme009,a~sme010,a~sme011,a~sme012 FROM cosr AS a INNER JOIN @gt_alv2 AS b ON right( a~objnr,10 ) = b~rcntr AND a~gjahr = b~gjahr WHERE a~stagr = @cns_at03 OR a~stagr = @cns_at04 OR a~stagr = @cns_at05 INTO TABLE @DATA(lt_hyl).