有一sql语句这样写的
select cast(e.B31Amount as int) as B31Amount, e.B20ID as B20ID,b.A07name as A07name,
cast(e.B22Amount as int) as B22Amount, Amount=isnull(rtrim(cast( case h.b31state when'1' then f.B31Samount end as int)),'')
from A07_Commodity b join B20_VipOrderDetail e on e.a07code=b.a07code left join B31_DeliverSource f on f.b20id=e.b20id
left join B31_DeliverBill h on h.B31code=f.B31code and h.b20code=e.b20code and h.b31state='1'
where e.b20id in (129,134) and e.b20code='200809041'
查询结果如下:
B31amount B20id A07NAME B22Amount Amount
4 129 游戏基地 10 10
0 134 东星磁性白板 8 4
0 134 东星磁性白板 8 3
想得到结果是这样的:
B31amount B20id A07NAME B22Amount Amount
4 129 游戏基地 10 10
0 134 东星磁性白板 8 7
分组查询的sql语句该怎样写呢??
select cast(e.B31Amount as int) as B31Amount, e.B20ID as B20ID,b.A07name as A07name,
cast(e.B22Amount as int) as B22Amount, Amount=isnull(rtrim(cast( case h.b31state when'1' then f.B31Samount end as int)),'')
from A07_Commodity b join B20_VipOrderDetail e on e.a07code=b.a07code left join B31_DeliverSource f on f.b20id=e.b20id
left join B31_DeliverBill h on h.B31code=f.B31code and h.b20code=e.b20code and h.b31state='1'
where e.b20id in (129,134) and e.b20code='200809041'
查询结果如下:
B31amount B20id A07NAME B22Amount Amount
4 129 游戏基地 10 10
0 134 东星磁性白板 8 4
0 134 东星磁性白板 8 3
想得到结果是这样的:
B31amount B20id A07NAME B22Amount Amount
4 129 游戏基地 10 10
0 134 东星磁性白板 8 7
分组查询的sql语句该怎样写呢??
cast(e.B22Amount as int) as B22Amount, Amount=sum(isnull(rtrim(cast( case h.b31state when'1' then f.B31Samount end as int)),'') )
from A07_Commodity b join B20_VipOrderDetail e on e.a07code=b.a07code left join B31_DeliverSource f on f.b20id=e.b20id
left join B31_DeliverBill h on h.B31code=f.B31code and h.b20code=e.b20code and h.b31state='1'
where e.b20id in (129,134) and e.b20code='200809041'
group by cast(e.B31Amount as int) ,e.B20ID,b.A07name ,cast(e.B22Amount as int)
from (select cast(e.B31Amount as int) as B31Amount, e.B20ID as B20ID,b.A07name as A07name,
cast(e.B22Amount as int) as B22Amount, Amount=isnull(rtrim(cast( case h.b31state when'1' then f.B31Samount end as int)),'')
from A07_Commodity b join B20_VipOrderDetail e on e.a07code=b.a07code left join B31_DeliverSource f on f.b20id=e.b20id
left join B31_DeliverBill h on h.B31code=f.B31code and h.b20code=e.b20code and h.b31state='1'
where e.b20id in (129,134) and e.b20code='200809041' ) t
group by B31amount,B20id,A07NAME,B22Amount
SELECT B31Amount,A07name,SUM(B22Amount) as B22Amount,SUM(Amount) as Amount
FROM
(select cast(e.B31Amount as int) as B31Amount,
e.B20ID as B20ID,b.A07name as A07name,
cast(e.B22Amount as int) as B22Amount,
Amount=isnull(rtrim(cast(
case h.b31state when'1' then f.B31Samount end as int)),''
)
from A07_Commodity b join B20_VipOrderDetail e
on e.a07code=b.a07code
left join B31_DeliverSource f
on f.b20id=e.b20id
left join B31_DeliverBill h
on h.B31code=f.B31code and h.b20code=e.b20code and h.b31state='1'
where e.b20id in (129,134)
and e.b20code='200809041'
)tmpGROUP BY B31Amount,A07name