具有的SQL查询存在，并且对子查询中的许多字段计数

Question

我有这个查询：

SELECT *
FROM j_1gvl_ml AS jml
WHERE sptag IN @s_sptag AND
bukrs = @p_bukrs AND
werks IN @s_werks AND
matnr IN @s_matnr AND
tract IN @s_tract AND
erdat IN @s_erdat AND
repid = @p_repid AND
EXISTS ( SELECT vrsio,whbcode,bukrs,belnr,buzei,
werks,locat,matnr,bwtar,orgn, COUNT( * )
FROM j_1gvl_ml
WHERE vrsio = jml~vrsio AND
sptag = jml~sptag AND
whbcode = jml~whbcode AND
bukrs = jml~bukrs AND
belnr = jml~belnr AND
buzei = jml~buzei AND
werks = jml~werks AND
locat = jml~locat AND
matnr = jml~matnr AND
bwtar = jml~bwtar AND
orgn = jml~orgn
GROUP BY vrsio,whbcode,bukrs,belnr,buzei,
werks,locat,matnr,bwtar,orgn
HAVING COUNT( * ) > 1  )
INTO TABLE @itj_1gvl_ml.

有一个错误，我在子查询中不能有超过1个字段。

我知道我可以对所有条目进行2次查询。但我正在寻找是否有可能，在一个查询中只有双、三等记录。

这可能吗？

Answer 1

EXISTS子查询用于确定要选择的行。它不会向结果集中添加行或列。

将子查询的SELECT子句中的列列表减少到任意一列，甚至减少到一个文本(如SELECT 1(，您将获得重复项：

SELECT *
FROM j_1gvl_ml AS jml
WHERE sptag IN @s_sptag AND
bukrs = @p_bukrs AND
werks IN @s_werks AND
matnr IN @s_matnr AND
tract IN @s_tract AND
erdat IN @s_erdat AND
repid = @p_repid AND
EXISTS ( SELECT belnr
FROM j_1gvl_ml
WHERE vrsio = jml~vrsio AND
sptag = jml~sptag AND
whbcode = jml~whbcode AND
bukrs = jml~bukrs AND
belnr = jml~belnr AND
buzei = jml~buzei AND
werks = jml~werks AND
locat = jml~locat AND
matnr = jml~matnr AND
bwtar = jml~bwtar AND
orgn = jml~orgn
GROUP BY vrsio,whbcode,bukrs,belnr,buzei,
werks,locat,matnr,bwtar,orgn
HAVING COUNT( * ) > 1  )
INTO TABLE @itj_1gvl_ml.