时间戳

首页 > 解决方案 > 根据两列组合中的数据删除行数据

sql - 根据两列组合中的数据删除行数据

问题描述

下面的代码产生左边的数据块

在此处输入图像描述

它为我提供了整个子集，但我只需要查看：对于所有 Variant_Status 为 I2 的相同 SATNR-VKORG 组合。如果任何 Variant_Status 不是 I2，则不要显示整个 SATNR-VKORG 组合。我的最终输出应该只是下面数据中的前两行，而所有其他行都不符合条件。

我不知道如何做到这一点，但我正在考虑使用像 right 块这样的计数函数来连接 SATNR-VKORG 和 SATNR-VKORG-Variant_Status 并对每个唯一组合进行计数。对于相同的 SATNR-VKORG 组合，如果两个计数相同，则表示显示 else 不显示。即使我不知道如何编码，任何人都可以帮助或有更好的主意？

SELECT TOP (1000) 
 MARA.MATNR, 
 MARA.SATNR, 
 MARA.ATTYP, 
 MARA.MTART, 
 MARA.MSTAE, 
 MARA.LVORM, 
 MVKE.VMSTA as Variant_Status, 
 MVKE.VTWEG, mvke.VKORG, MVKE2.
 VMSTA as Generic_Status, 
 MVKE2.VTWEG, MVKE2.VKORG, 
 mara.satnr + mvke.vkorg as concated

from [dgSAP_PRD].dbo.MARA AS MARA
JOIN [dgSAP_PRD].dbo.MVKE AS MVKE ON MARA.MATNR = MVKE.MATNR
JOIN [dgSAP_PRD].dbo.MARA AS MARA2 ON MARA.SATNR = MARA2.MATNR
JOIN [dgSAP_PRD].dbo.MVKE AS MVKE2 ON MARA2.MATNR = MVKE2.MATNR

WHERE MARA.MTART != 'ZODE' 
  AND MARA.ATTYP in (02) 
  AND MARA.LVORM = '' 
  AND MVKE2.VTWEG = '34' 
  AND MVKE.VTWEG = '34' 
  AND MVKE.VKORG=MVKE2.VKORG 
  and mvke2.vmsta != 'I2'

ORDER BY MARA.SATNR,MVKE.VKORG,MVKE2.VKORG, MARA.MATNR

标签： sqlsql-server

解决方案

WITH cte AS 
(
SELECT TOP (1000) 
 MARA.MATNR, 
 MARA.SATNR, 
 MARA.ATTYP, 
 MARA.MTART, 
 MARA.MSTAE, 
 MARA.LVORM, 
 MVKE.VMSTA as Variant_Status, 
 MVKE.VTWEG, mvke.VKORG, MVKE2.
 VMSTA as Generic_Status, 
 MVKE2.VTWEG, MVKE2.VKORG, 
 mara.satnr + mvke.vkorg as concated,
 SUM(CASE WHEN Variant_Status <> 'I2' THEN 1 ELSE 0 END) OVER (PARTITION BY SATNR, VKORG) marker 
 
from [dgSAP_PRD].dbo.MARA AS MARA
JOIN [dgSAP_PRD].dbo.MVKE AS MVKE ON MARA.MATNR = MVKE.MATNR
JOIN [dgSAP_PRD].dbo.MARA AS MARA2 ON MARA.SATNR = MARA2.MATNR
JOIN [dgSAP_PRD].dbo.MVKE AS MVKE2 ON MARA2.MATNR = MVKE2.MATNR

WHERE MARA.MTART <> 'ZODE' 
  AND MARA.ATTYP in (02) 
  AND MARA.LVORM = '' 
  AND MVKE2.VTWEG = '34' 
  AND MVKE.VTWEG = '34' 
  AND MVKE.VKORG=MVKE2.VKORG 
  and mvke2.vmsta <> 'I2'
)

SELECT *
FROM cte
WHERE marker = 0
ORDER BY SATNR, VKORG, VKORG, MATNR

推荐阅读