在线求一条SQL语句 case when 的

[兔子侠客]

描述： 数据库字段

pk_group pk_org pk_order orderstate ordertype money

现在 需要
************************************************************************************************
当 orderstate =3 并且 ordertype !=4 时，统计所有 money 之和 即 sum(money )
同时
当 ordertype !=4 时，统计所有 money 之和 即 sum(money )
并且根据 pk_group pk_org pk_order 分组

*************************************************************************************************

我写的：

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       t.pk_currtype as corigcurrencyid,
       t.pk_order as pk_order
case  
  WHEN orderstate  =3  and  ordertype !=4    THEN sum(money )   as A 

   WHEN   ordertype !=4    THEN sum(money )   as B

from  table  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

求各位大神帮忙 修改一下 多谢

[bw555]

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       t.pk_currtype as corigcurrencyid,
       t.pk_order as pk_order,
SUM(case  
  WHEN orderstate=3 and ordertype!=4 THEN money 
  WHEN ordertype!=4 THEN money END) AS A
from  table  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

[CT_LXL]

t.pk_currtype 应该加到group by中，不然要报错

[bw555]

恩,同意楼上意见，也可以使用max

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       max(t.pk_currtype) as corigcurrencyid,
       t.pk_order as pk_order,
SUM(case  
  WHEN orderstate=3 and ordertype!=4 THEN money 
  WHEN ordertype!=4 THEN money END) AS A
from  table  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

[兔子侠客]

引用 1 楼 bw555 的回复:

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       t.pk_currtype as corigcurrencyid,
       t.pk_order as pk_order,
SUM(case  
  WHEN orderstate=3 and ordertype!=4 THEN money 
  WHEN ordertype!=4 THEN money END) AS A
from  table  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

多谢啦 这样就好了

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       
       t.pk_order as pk_order,
SUM(case  
  WHEN ORDERSTATUS=3 and T.ordertype!=4 THEN money   END)AS AA,
 
SUM( case WHEN ORDERTYPE=4 THEN money  END)AS BB
from  table1  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

[yuyeyi]

ordertype !=4 不是包含了 orderstate =3 并且 ordertype !=4 吗？

[bw555]

引用 5 楼 yuyeyi 的回复:

ordertype !=4 不是包含了 orderstate =3 并且 ordertype !=4 吗？

是笔误吧 第二个应该是ORDERTYPE=4

[卖水果的net]

sum 拿到外面。 group by 的列表和 select 中的未使用聚合函数的列，保持一致。

[L-B-J]

select t.pk_group as pk_group, t.pk_org as pk_org, t.pk_order as pk_order, SUM(case WHEN orderstate=3 and ordertype!=4 THEN money WHEN ordertype=4 THEN money END) AS CNT from table t group by t.pk_order, t.pk_group, t.pk_org

[华而不实]

引用 4 楼 skyxuyan 的回复:

[quote=引用 1 楼 bw555 的回复:]

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       t.pk_currtype as corigcurrencyid,
       t.pk_order as pk_order,
SUM(case  
  WHEN orderstate=3 and ordertype!=4 THEN money 
  WHEN ordertype!=4 THEN money END) AS A
from  table  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

多谢啦 这样就好了

select t.pk_group as pk_group,
       t.pk_org as pk_org,
       
       t.pk_order as pk_order,
SUM(case  
  WHEN ORDERSTATUS=3 and T.ordertype!=4 THEN money   END)AS AA,
 
SUM( case WHEN ORDERTYPE=4 THEN money  END)AS BB
from  table1  t
group by t.pk_order,
          t.pk_group,
          t.pk_org

[/quote] 同意