實現如下SQL語句
我有如下表？現在要求根據如下表得出 TmpTabe
ColorNo XXS XS S M L XL XXL
001 10 10 10 0 0 0 0
001 0 10 0 0 5 0 0
002 20 20 0 0 0 0 0
(TmpTabel)
ColorNo Size TotalCount
001 XXS 10
001 XS 20
001 S 10
001 L 5
002 XXS 20
002 XS 20
[解决办法]
select * from (
select ColorNo, 'XXS ' as Size,sum(XXS) as TotalCount
from TmpTabel
group by ColorNo
union all
select ColorNo, 'XS ' as Size,sum(XS)
from TmpTabel
group by ColorNo
union all
select ColorNo, 'S ' as Size,sum(S)
from TmpTabel
group by ColorNo
union all
select ColorNo, 'M ' as Size,sum(M)
from TmpTabel
group by ColorNo
union all
select ColorNo, 'L ' as Size,sum(L)
from TmpTabel
group by ColorNo
union all
select ColorNo, 'XL ' as Size,sum(XL)
from TmpTabel
group by ColorNo
union all
select ColorNo, 'XXL ' as Size,sum(XXL)
from TmpTabel
group by ColorNo)t
where TotalCount> 0
[解决办法]
select ColorNo, 'XXS ' Size,sum(XXS) TotalCount from TmpTabe union
select ColorNo, 'XS ' ,sum(XXS) from TmpTabe union
select ColorNo, 'S ' ,sum(XXS) from TmpTabe union
select ColorNo, 'M ' ,sum(XXS) from TmpTabe union
select ColorNo, 'L ' ,sum(XXS) from TmpTabe union
select ColorNo, 'XL ' ,sum(XXS) from TmpTabe union
select ColorNo, 'XXL ',sum(XXS) from TmpTabe
