iT邦幫忙

0

MS-SQL語法的問題

sql
  • 分享至 

  • xImage

NO 品名 單價 條碼
1__A__20__001
1__A__20__002
2__B__30__011
3__A__20__001
2__B__30__012
1__A__20__003
3__B__30__011

NO 1是廠內生產
NO 2是外貨購入
NO 3是出貨
請教各位大大
我要如何寫出讓 庫存-出貨 也就是說 1+2-3 明細表

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
sten
iT邦新手 2 級 ‧ 2014-07-02 16:51:28
最佳解答

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

0
純真的人
iT邦大師 1 級 ‧ 2014-05-13 12:01:31

select Sum(單價) as 總計 from ((
select 單價 from 倉儲 where NO in(1,2)
)union(
select -(單價) from 倉儲 where NO in(3)
)) as k

ununk iT邦新手 5 級 ‧ 2014-05-15 14:35:29 檢舉

抱歉! 我沒有寫清楚@@
我需要列出明細,而且上面的條碼要正確的,所以只能針對條碼去抵銷

0
gheing
iT邦新手 5 級 ‧ 2014-05-14 08:58:12

提供參考:

Select 品名 , Sum( Case No When 3 than 數量 * -1 else 數量 End ) as 結餘數量
From 資料表名稱
Group By 品名

ununk iT邦新手 5 級 ‧ 2014-05-15 14:31:16 檢舉

抱歉大大,我沒寫清楚,我需要明細,而且上面扣掉的條碼要是正確的@@

0
luckymoon
iT邦新手 5 級 ‧ 2014-05-15 10:31:16

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 品名

ununk iT邦新手 5 級 ‧ 2014-05-15 14:32:12 檢舉

我需要的明細條碼要正確的@@"

ununk iT邦新手 5 級 ‧ 2014-06-12 16:19:55 檢舉

數量是? 我的表裡面沒有數量,一個產品一個條碼

2
liam0318
iT邦新手 5 級 ‧ 2014-05-16 09:39:28

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.條碼

2
rogeryao
iT邦超人 7 級 ‧ 2014-05-16 12:35:01

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

0
bluesky1213
iT邦新手 3 級 ‧ 2014-06-20 13:28:17

ununk提到:
NO 品名 單價 條碼

你的意思是說3不要出來嗎?
直接多個條件過濾3不要出現就好了啊
Select NO, 品名, 單價, 條碼
from tablename
where NO <>3

我要發表回答

立即登入回答