从合并的查询中删除重复项-DISTINCT和UNION不起作用

2020-09-18 16:42发布

点击此处---> 群内免费提供SAP练习系统(在群公告中)加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)大家好, 我想删除结果中重...

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


大家好,

我想删除结果中重复的ItemCode行。

保留第一个查询中的所有内容,并从第二个查询中删除那些已经在第一个查询中的内容。 事实是,其他列的某些值不相同,因此DISTINCT或UNION无法正常工作,但是如果我想保持最大的良好记录,则需要保持其他列的值不匹配。

我附了一个结果示例。 预先感谢!

这是我现在拥有的;

 SELECT DISTINCT T1。[ItemCode],
 T2。[ItemName],
 SUM((((T1.Quantity)/365)* 1.15)作为"平均每日使用量",
 (T2。[OnHand]-T2。[IsCommited])为"可用",
 (T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)作为'Reste',
 T2。[LeadTime],
 MIN(T4。[ShipDate])作为"下一个交货日期",
 T4。[数量],案例
 当(T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)> 0 THEN'OK'
 当(T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)<= 0 AND(T2。[OnHand] + T4。[Quantity]  ]-T2。[IsCommited])-(DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))*(SUM(T1.Quantity)/365)* 1.15)> 0然后'下次发货就可以了 '
 时间(T2。[手头]-T2。[已提交])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)<= 0然后'***警告***'
 当T2。[LeadTime]为NULL并且(T2。[OnHand] + T4。[Quantity]-T2。[IsCommited])-(DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))*(SUM  (T1。数量)/365)* 1.15)> 0然后'确定'
 当T2。[LeadTime]为NULL并且(T2。[OnHand] + T4。[Quantity]-T2。[IsCommited])-(DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))*(SUM  (T1数量)/365)* 1.15)<= 0然后'***警告***'
 END作为"州",
 DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))作为"直到交货的天数"


 从OIGE T0
 内联接IGE1 T1到T0。[DocEntry] = T1。[DocEntry]
 内部联接OITM T2在T2上。[ItemCode] = T1。[ItemCode]
 T3上的内部联接OITB T3。[ItmsGrpCod] = T2。[ItmsGrpCod]
 内部联接POR1 T4在T2上。[ItemCode] = T4。[ItemCode]


 T2。[PrchseItem] ='Y'AND T2。[validFor] ='Y'AND T2。[TreeType] <>'P'AND T0.Docdate> = DATEADD(Year,-1,GETDATE())和左 (T1。[ItemCode],2)<>'O-'和T4。[ShipDate]> = GETDATE()
 GROUP BY T1。[ItemCode],T2。[ItemName],T2。[OnHand],T2。[已提交],T2。[LeadTime],T4。[数量],T2。[OnOrder]


 全联盟


 SELECT DISTINCT T1。[ItemCode],
 T2。[ItemName],
 SUM((((T1.Quantity)/365)* 1.15)作为"平均每日使用量",
 (T2。[OnHand]-T2。[IsCommited])为"可用",
 (T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)作为'Reste',
 T2。[LeadTime],
 空值,
 空,大写
 当(T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)> 0 THEN'OK'
 时间(T2。[手头]-T2。[已提交])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)<= 0然后'***警告***'
 当T2。[LeadTime]为NULL时'------------'
 END作为"州",
 ''


 从OIGE T0
 内联接IGE1 T1到T0。[DocEntry] = T1。[DocEntry]
 内部联接OITM T2在T2上。[ItemCode] = T1。[ItemCode]
 T3上的内部联接OITB T3。[ItmsGrpCod] = T2。[ItmsGrpCod]




 T2。[PrchseItem] ='Y'AND T2。[validFor] ='Y'AND T2。[TreeType] <>'P'AND T0.Docdate> = DATEADD(Year,-1,GETDATE())和左 (T1。[ItemCode],2)<>'O-'
 GROUP BY T1。[ItemCode],T2。[ItemName],T2。[OnHand],T2。[IsCommited],T2。[LeadTime],T2。[OnOrder]


 T1的订单。[ItemCode] t21.jpg
 
t21.jpg (59.8 kB)
2条回答
木偶小白
2020-09-18 17:07

非常感谢Johan!

我做了一些小的修改,现在我已经修改了

/*第一个查询作为内联视图*/
 ;与Q1(ItemCode,ItemName,[平均每日使用量],可用,[左侧],LeadTime,[下一个交货日期],[要交货的数量],[状态],[直到交货的天数],[买方],[  CardCode],[CardName])
 AS(选择T1。[ItemCode],
 T2。[ItemName],
 SUM((((T1.Quantity)/365)* 1.15)作为"平均每日使用量",
 (T2。[OnHand]-T2。[IsCommited])为"可用",
 (T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)为'Left',
 T2。[LeadTime],
  MIN(T4。[ShipDate])作为"下一个交货日期",
 T4。[数量]作为"要交付的数量",
 案件
  当(T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)> 0 THEN'OK'
  当(T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)<= 0 AND(T2。[OnHand] + T4。[Quantity]  ]-T2。[已提交])-(DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))*(SUM(T1.Quantity)/365)* 1.15)> 0然后'下次发货就可以了 '
  时间(T2。[手头]-T2。[已提交])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)<= 0然后'***警告***'
  当T2。[LeadTime]为NULL并且(T2。[OnHand] + T4。[Quantity]-T2。[IsCommited])-(DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))*(SUM  (T1数量)/365)* 1.15)> 0然后'确定'
  当T2。[LeadTime]为NULL并且(T2。[OnHand] + T4。[Quantity]-T2。[IsCommited])-(DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))*(SUM  (T1数量)/365)* 1.15)<= 0然后'***警告***'
 END作为"州",
 DATEDIFF(DD,GETDATE(),MIN(T4。[ShipDate]))作为"直到交货的天数",
 案件
 当T2。[QryGroup7] ='Y'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='Y'时,"所有买家"
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='N'时'没有买家'
 当T2。[QryGroup7] ='Y'AND T2。[QryGroup8] ='N'AND T2。[QryGroup9] ='N'AND T2。[QryGroup10] ='N'然后是'Exacta'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='N'然后'Acheteur 1'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='N'然后'Acheteur 2'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='Y'然后'Acheteur 3'
 当T2。[QryGroup7] ='Y'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='N'之后,"精确交易员1"
 当T2。[QryGroup7] ='Y'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='N'之后,"精确交易员2"
 当T2。[QryGroup7] ='Y'AND T2。[QryGroup8] ='N'AND T2。[QryGroup9] ='N'AND T2。[QryGroup10] ='Y'然后'Exacta etchetcher 3'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='N'时'Acheteur 1 et Acheteur 2'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='Y'时'Acheteur 1 et Acheteur 3'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='Y'时'Acheteur 2 et Acheteur 3'
 ELSE" Voir Item"
 END AS'Buyer',T2。[CardCode],T5。[CardName]
 从OIGE T0
 内联接IGE1 T1到T0。[DocEntry] = T1。[DocEntry]
 内部联接OITM T2在T2上。[ItemCode] = T1。[ItemCode]
 T3上的内部联接OITB T3。[ItmsGrpCod] = T2。[ItmsGrpCod]
 内部联接POR1 T4在T2上。[ItemCode] = T4。[ItemCode]
 左外连接OCRD T5在T2上。[CardCode] = T5。[CardCode]
 在T2。[PrchseItem] ='Y'
   AND T2。[validFor] ='Y'
   AND T2。[TreeType] <>'P'
   AND T0.Docdate> = DATEADD(年,-1,GETDATE())
   AND LEFT(T1。[ItemCode],2)<>'O-'
   AND T4。[ShipDate]> = GETDATE()
 GROUP BY T1。[ItemCode],T2。[ItemName],T2。[OnHand],T2。[IsCommited],T2。[LeadTime],T4。[Quantity],T2。[OnOrder],T4。[ShipDate],  T2。[QryGroup7],T2。[QryGroup8],T2。[QryGroup9],T2。[QryGroup10],T2。[CardCode],T5。[CardName])
/*第二个查询作为内联视图*/
 ,第2季度(ItemCode,ItemName,[平均每日使用量],可用,Reste,LeadTime,[下一个交货日期],[要交货的数量],[状态],[待交货的天数],[买方],[CardCode],  [CardName])
 AS(选择T1。[ItemCode],
 T2。[ItemName],
 SUM((((T1.Quantity)/365)* 1.15)作为"平均每日使用量",
 (T2。[OnHand]-T2。[IsCommited])为"可用",
 (T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)为'Left',
 T2。[LeadTime],
 空值,
 空,大写
 当(T2。[OnHand]-T2。[IsCommited])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)> 0 THEN'OK'
 时间(T2。[手头]-T2。[已提交])-(T2。[LeadTime] *(SUM(T1.Quantity)/365)* 1.15)<= 0然后'***警告***'
 当T2。[LeadTime]为NULL时,然后str((T2。[OnHand]-T2。[IsCommited])/SUM((((T1.Quantity)/365)* 1.15),19,0)+'天,直到结束 股票'
 END作为"州",
 '',
 案件
 当T2。[QryGroup7] ='Y'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='Y'时,"所有买家"
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='N'时'没有买家'
 当T2。[QryGroup7] ='Y'AND T2。[QryGroup8] ='N'AND T2。[QryGroup9] ='N'AND T2。[QryGroup10] ='N'然后是'Exacta'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='N'然后'Acheteur 1'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='N'然后'Acheteur 2'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='Y'然后'Acheteur 3'
 当T2。[QryGroup7] ='Y'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='N'之后,"精确交易员1"
 当T2。[QryGroup7] ='Y'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='N'之后,"精确交易员2"
 当T2。[QryGroup7] ='Y'AND T2。[QryGroup8] ='N'AND T2。[QryGroup9] ='N'AND T2。[QryGroup10] ='Y'然后'Exacta etchetcher 3'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='N'时'Acheteur 1 et Acheteur 2'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='Y'和T2。[QryGroup9] ='N'和T2。[QryGroup10] ='Y'时'Acheteur 1 et Acheteur 3'
 当T2。[QryGroup7] ='N'和T2。[QryGroup8] ='N'和T2。[QryGroup9] ='Y'和T2。[QryGroup10] ='Y'时'Acheteur 2 et Acheteur 3'
 ELSE" Voir Item"
 END AS'买方',T2。[CardCode],T5。[CardName]
 从OIGE T0
 内联接IGE1 T1到T0。[DocEntry] = T1。[DocEntry]
 内部联接OITM T2在T2上。[ItemCode] = T1。[ItemCode]
 T3上的内部联接OITB T3。[ItmsGrpCod] = T2。[ItmsGrpCod]
 左外连接OCRD T5在T2上。[CardCode] = T5。[CardCode]
 在T2。[PrchseItem] ='Y'
   AND T2。[validFor] ='Y'
   AND T2。[TreeType] <>'P'
   AND T0.Docdate> = DATEADD(年,-1,GETDATE())
   AND LEFT(T1。[ItemCode],2)<>'O-'
 GROUP BY T1。[ItemCode],T2。[ItemName],T2。[OnHand],T2。[IsCommited],T2。[LeadTime],T2。[OnOrder],T2。[QryGroup7],T2。[QryGroup8],  T2。[QryGroup9],T2。[QryGroup10],T2。[CardCode],T5。[CardName])
/*最后我们查询两个视图*/
 选择 *
 从第一季度开始
 联盟
 选择 *
 从第二季度开始
 Q2.ItemCode不在的位置(从Q1中选择ItemCode)
 订单项编号
 

一周热门 更多>