时间戳

首页 > 解决方案 > 根据每个条件对值进行分组

sql - 根据每个条件对值进行分组

问题描述

我有三个表，如 Sip、SipLine 和 Hareket，我想加入这些表并获得一些结果。正如你在下面看到的，我想要得到的是根据每个 Kalite 的 No 总量。通过我所做的，我得到了多行，但我不想这样做。

我的查询是：

>select 
Sip.No,
Sip.DetNo,
CASE WHEN H.Kal = '1' THEN
SUM(H.Miktar) 
ELSE
null END as Kalite1,
CASE WHEN H.Kal = '2' THEN
SUM(H.Miktar) 
ELSE
null END as Kalite2,
CASE WHEN H.Kal = '3' THEN
SUM(H.Miktar) 
ELSE
null END as Kalite3,
CASE WHEN H.Kal = '4' THEN
SUM(H.Miktar) 
ELSE
null END as Kalite4,
CASE WHEN H.Kal = '5' THEN
SUM(H.Miktar) 
ELSE
null END as Kalite5


>from Sip





>left join SipSatir SipL on
Sip.Srkt = SipL.SipSatirSrkt and
Sip.Say = SipL.SipSatirSay and
Sip.No = SipL.SipSatirNo


>left join Hareket H on
H.Srkt = SipL.SALESipRDERSrkt and 
H.DetNo = SipL.DetNo and
H.MNo = SipL.MKNo and
H.DKODNo01 = SipL.KODNo01 and
H.DKODNo02 = SipL.KODNo02 and
H.DKODNo03 = SipL.KODNo03 and
H.DKODNo04 = SipL.KODNo04 and
H.DKODNo05 = SipL.KODNo05 and
H.DKODNo06 = SipL.KODNo06 and
H.DKODNo07 = SipL.KODNo07 and
H.DKODNo08 = SipL.KODNo08 and
H.DKODNo09 = SipL.KODNo09 and
H.DKODNo10 = SipL.KODNo10


>where H.TEMPLATENo = '102' and H.MNo in ('50','60') and H.DetNo <> ''

>group by
Sip.No,
Sip.DetNo,
H.Kal

输出：

不 识别号 卡利特1 KALITE2 卡利特3 KALITE4 卡利特5
202001322-2 202001322-2 733.74 -------------- -------------- -------------- --------------
202001322-2 202001322-2 -------------- 314.22 -------------- -------------- --------------
202001322-2 202001322-2 -------------- -------------- -------------- -------------- 19.32
202001933 202001933 199.65 -------------- -------------- -------------- --------------
202002366-1 202002366 147.7 -------------- -------------- -------------- --------------
202002366-1 202002366 -------------- 26.81 -------------- -------------- --------------

我想得到以下结果：

不 识别号 卡利特1 KALITE2 卡利特3 KALITE4 卡利特5
202001322-2 202001322-2 733.74 314.22 -------------- -------------- 19.32
202001933 202001933 199.65 -------------- -------------- -------------- --------------
202002366-1 202002366 147.7 26.81 -------------- -------------- --------------

我怎样才能得到这个？

标签： sqldb2

解决方案

你只需要使用CASE里面的聚合函数（称为条件聚合），不要使用kal如下group by：

>select 
Sip.No,
Sip.DetNo,
SUM(CASE WHEN H.Kal = '1' THEN H.Miktar END) as Kalite1,
SUM(CASE WHEN H.Kal = '2' THEN H.Miktar END) as Kalite2,
SUM(CASE WHEN H.Kal = '3' THEN H.Miktar END) as Kalite3,
SUM(CASE WHEN H.Kal = '4' THEN H.Miktar END) as Kalite4,
SUM(CASE WHEN H.Kal = '5' THEN H.Miktar END) as Kalite5
>from Sip
>left join SipSatir SipL on
Sip.Srkt = SipL.SipSatirSrkt and
Sip.Say = SipL.SipSatirSay and
Sip.No = SipL.SipSatirNo
>left join Hareket H on
H.Srkt = SipL.SALESipRDERSrkt and 
H.DetNo = SipL.DetNo and
H.MNo = SipL.MKNo and
H.DKODNo01 = SipL.KODNo01 and
H.DKODNo02 = SipL.KODNo02 and
H.DKODNo03 = SipL.KODNo03 and
H.DKODNo04 = SipL.KODNo04 and
H.DKODNo05 = SipL.KODNo05 and
H.DKODNo06 = SipL.KODNo06 and
H.DKODNo07 = SipL.KODNo07 and
H.DKODNo08 = SipL.KODNo08 and
H.DKODNo09 = SipL.KODNo09 and
H.DKODNo10 = SipL.KODNo10
>where H.TEMPLATENo = '102' and H.MNo in ('50','60') and H.DetNo <> ''
>group by
Sip.No,
Sip.DetNo

推荐阅读