create table #atemp (
[Number] CHAR(1),
[Item] CHAR(1),
[Price] INT,
[Barcode] VARCHAR(3)
);
insert into #atemp VALUES ('1','A','20','001');
insert into #atemp VALUES ('1','A','20','002');
insert into #atemp VALUES ('2','B','30','011');
insert into #atemp VALUES ('3','A','20','001');
insert into #atemp VALUES ('2','B','30','012');
insert into #atemp VALUES ('1','A','20','003');
insert into #atemp VALUES ('3','B','30','011');
SELECT A.Item, A.Barcode , A.Price , A.STKQTY AS 期初庫存, ISNULL(B.SHIPQTY,0) AS 出貨數量, A.STKQTY - ISNULL(B.SHIPQTY,0) AS 期末數量
FROM (
SELECT [Item],[Barcode],[Price],COUNT(*) AS STKQTY
FROM #atemp
WHERE [Number] = '1'
OR [Number] = '2'
GROUP BY [Item],[Barcode],[Price]) AS A LEFT JOIN (
SELECT [Item],[Barcode],[Price],COUNT(*) AS SHIPQTY
FROM #atemp
WHERE [Number] = '3'
GROUP BY [Item],[Barcode],[Price]) AS B ON A.Item = B.Item AND A.Barcode = B.Barcode
select Sum(單價) as 總計 from ((
select 單價 from 倉儲 where NO in(1,2)
)union(
select -(單價) from 倉儲 where NO in(3)
)) as k
提供參考:
Select 品名 , Sum( Case No When 3 than 數量 * -1 else 數量 End ) as 結餘數量
From 資料表名稱
Group By 品名
SELECT 品名,
(SELECT SUM(數量) FROM 資料表 WHERE NO = '1' AND 品名 = 品名) +
(SELECT SUM(數量) FROM 資料表 WHERE NO = '2' AND 品名 = 品名) -
(SELECT SUM(數量) FROM 資料表 WHERE NO = '3' AND 品名 = 品名) AS 庫存
FROM 資料表
GROUP BY 品名
create table #temp (no int,品名 varchar(1),單價 int,條碼 varchar(3))
insert into #temp
select 1,'A',20,'001'
union all
select 1,'A',20,'002'
union all
select 2,'B',30,'011'
union all
select 3,'A',20,'001'
union all
select 2,'B',30,'012'
union all
select 1,'A',20,'003'
union all
select 3,'B',30,'011'
SELECT main.*,(ISNULL(sub.單價,0) + ISNULL(sub1.單價,0)) - ISNULL(sub2.單價,0) as 計算結果
from #temp as main
left join (select * from #temp where no = 1) as sub
on main.條碼 = sub.條碼
left join (select * from #temp where no = 2) as sub1
on main.條碼 = sub1.條碼
left join (select * from #temp where no = 3) as sub2
on main.條碼 = sub2.條碼
table :e
e1:NO
e2:品名
e3:單價
e4:條碼
select e2 as '品名',e4 '條碼',
sum(case when (e1=1 or e1=2) then e3 else -e3 end) as '總計'
from e
group by e2,e4
order by e2,e4
ununk提到:
NO 品名 單價 條碼
你的意思是說3不要出來嗎?
直接多個條件過濾3不要出現就好了啊
Select NO, 品名, 單價, 條碼
from tablename
where NO <>3