0

## MS SQL相同資料不同計算

### 4 個回答

0
Albert
iT邦高手 1 級 ‧ 2016-08-29 16:45:17

``````SELECT x,商品, SUM(x.計價數量),SUM(x.不計價數量), SUM(x.金額)
FROM(
SELECT a,商品,
CASE WHEN a.金額>0 THEN  a.數量 ELSE 0 END AS  計價數量,
CASE WHEN a.金額=0 THEN  a.數量 ELSE 0 END AS  不計價數量,
a.金額
FROM  交易資料 a
) x
GROUP BY x.商品
``````

0

iT邦超人 1 級 ‧ 2016-08-26 17:46:49

0

iT邦高手 1 級 ‧ 2016-08-26 18:14:41

Three Ways To Transpose Rows Into Columns in Oracle SQL

``````select
銷售商品
,Sum(數量) as 數量
,Sum(無金額數量) as 無金額數量
,Sum(金額) as 金額
from (
select
銷售商品
,(Case when 金額 > 0 then Sum(數量) else 0 end) as 數量
,(Case when 金額 = 0 then Sum(數量) else 0 end) as 無金額數量
,Sum(金額) as 金額
from (
select *
from ((
select '衣服' 銷售商品,3 as 數量 ,0 as 金額
)union(
select '衣服' 銷售商品,2 as 數量 ,200 as 金額
)) as k
) as k
group by 銷售商品,金額
) as k
group by 銷售商品
``````
2

iT邦大師 1 級 ‧ 2016-08-26 18:21:27
``````
create table ithelp160826 (
id smallserial
, prod text not null
, qty int not null
, amount int not null
);

insert into ithelp160826 values
(default, '衣服', 3, 0),
(default, '衣服', 2, 200);

with
x as (
select prod
, sum(qty)
from ithelp160826
where amount = 0
group by prod
),
y as (
select prod
, sum(qty)
from ithelp160826
where amount != 0
group by prod
),
z as (
select prod
, sum(amount)
from ithelp160826
group by prod
)
select z.prod as "銷售商品"
, y.sum as "數量"
, x.sum as "無金額數量"
, z.sum as "金額"
from x
, y
, z
where x.prod = y.prod
and y.prod = z.prod;

+----------+------+------------+------+
| 銷售商品 | 數量 | 無金額數量 | 金額 |
+----------+------+------------+------+
| 衣服     |    2 |          3 |  200 |
+----------+------+------------+------+
(1 row)

``````