iT邦幫忙

1

MS SQL將數商品數量除以2,但必須符合基本撥量的倍數

Dear 各位先進
請教如何將 a.安全量 的值除以2(/2)
且必須符合為 b.基本撥量 的倍數?

商品貨號0011092為例(圖1)
若將安全量48除以2,會變成24,符合基本撥量2的倍數

而如果以商品貨號0015584為例(圖2)
安全量16,除以2會變成8,並不符合基本撥量3的倍數
所以必須變成9,而非6(用較多的原則)

有勞解惑,謝謝

SELECT a.商品貨號,a.安全量,b.基本撥量 
FROM 門市安全量基本檔 AS a
RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1'

(圖1)
https://ithelp.ithome.com.tw/upload/images/20180621/20011133jDeo733S7N.jpg
(圖2)
https://ithelp.ithome.com.tw/upload/images/20180621/20011133TFXuFwhc2k.jpg

sjzgo iT邦新手 4 級 ‧ 2018-06-22 10:34:16 檢舉
謝謝暐翰,請問如果要直接把現有資料庫的a.安全量 Update,應如何下手?
暐翰 iT邦大師 7 級 ‧ 2018-06-22 11:26:30 檢舉
我更新你要的

2 個回答

4
暐翰
iT邦大師 7 級 ‧ 2018-06-21 19:16:54
最佳解答
with CTE as (
    SELECT a.商品貨號,a.安全量,b.基本撥量 
    FROM 門市安全量基本檔 AS a
    RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
    WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1'
)
select [商品貨號],
        [安全量] [原安全量],
	[基本撥量],
	case when ([安全量]/2) % [基本撥量] = 0 then [安全量]/2
	else [基本撥量] * (([安全量]/2)/[基本撥量]+1)
	end  [新安全量]
from CTE

結果:

商品貨號 原安全量 基本撥量 新安全量
11092 48 4 24
1103 36 3 18
15584 16 3 9

線上測試連結

看更多先前的回應...收起先前的回應...
sjzgo iT邦新手 4 級 ‧ 2018-06-22 12:18:29 檢舉

謝謝暐翰,依照更新測試,收到了一個錯誤訊息(如下圖),另外抱歉沒有提到,小弟的SQL SERVER版本是2000
https://ithelp.ithome.com.tw/upload/images/20180622/20011133gw22TbVAmK.jpg

SELECT * From 
(SELECT a.商品貨號,a.安全量,b.基本撥量 
FROM 門市安全量基本檔 AS a
RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1') As CTE

with CTE as (
	select [商品貨號],
	        [安全量] [原安全量],
		[基本撥量],
		case when ([安全量]/2) % [基本撥量] = 0 then [安全量]/2
		else
		 [基本撥量] * (([安全量]/2)/[基本撥量]+1)
		end  [新安全量]
	from  CTE
)
update T
set [安全量] = [新安全量]
from (
	select T1.*,T2.[新安全量] from 門市安全量基本檔 T1
	left join CTTT T2 on T1.[商品貨號] = T2.[商品貨號]
	where T2.[商品貨號] is not null
) T;

select * from 門市安全量基本檔;
暐翰 iT邦大師 7 級 ‧ 2018-06-22 12:55:53 檢舉

改成這個

update T
set [安全量] = [新安全量]
from (
    SELECT a.商品貨號,a.安全量,b.基本撥量 ,
	    case when (a.安全量/2) % b.基本撥量 = 0 then a.安全量/2
	    else b.基本撥量 * ((a.安全量/2)/b.基本撥量+1)
	    end  [新安全量]
    FROM 門市安全量基本檔 AS a
    RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
    WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1'
) T

更新前最好檢查一下select出來結果是不是你要的,並且備份一下

select * into 門市安全量基本檔_0622_back from 門市安全量基本檔
sjzgo iT邦新手 4 級 ‧ 2018-06-22 14:20:07 檢舉

謝謝暐翰,已先將TABLE備份。
已SELECT結果來看,資料是正確的沒錯,但是這樣子UPDATE的話,是否會讓門市安全量基本檔Table多出一個新安全量的欄位呢?
https://ithelp.ithome.com.tw/upload/images/20180622/200111337B1C7ZAz63.jpg

暐翰 iT邦大師 7 級 ‧ 2018-06-22 15:50:07 檢舉

不會 因為以新安全量更新安全量

sjzgo iT邦新手 4 級 ‧ 2018-06-22 17:33:12 檢舉

有錯誤訊息:
https://ithelp.ithome.com.tw/upload/images/20180622/20011133JiBwVCiuJz.jpg

暐翰 iT邦大師 7 級 ‧ 2018-06-22 19:49:17 檢舉
select * into #temp_table from (
    SELECT a.商品貨號 取代商品貨號,a.安全量,b.基本撥量 ,
	    case when (a.安全量/2) % b.基本撥量 = 0 then a.安全量/2
	    else b.基本撥量 * ((a.安全量/2)/b.基本撥量+1)
	    end  [新安全量]
    FROM 門市安全量基本檔 AS a
    RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
    WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1'
) T

update 門市安全量基本檔  
set 安全量 = (select [新安全量] from #temp_table  where 商品貨號 = 取代商品貨號)
where 商品貨號 in (select 取代商品貨號 from #temp_table )

改用臨時表紀錄更新在實體表

測試連結

fysh711426 iT邦研究生 5 級 ‧ 2018-06-22 22:52:27 檢舉

Google 了一下,發現 暐翰 大大的範例可以改成這樣,不過不知道在 SQL SERVER 2000 上可不可以正確執行。

update a
set a.[安全量] = 
    case when (a.安全量/2) % b.基本撥量 = 0 then a.安全量/2
    else b.基本撥量 * ((a.安全量/2)/b.基本撥量+1) 
    end
FROM 門市安全量基本檔 AS a
RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1'
sjzgo iT邦新手 4 級 ‧ 2018-06-22 23:16:24 檢舉

下禮拜上班試試看

sjzgo iT邦新手 4 級 ‧ 2018-06-25 17:34:21 檢舉

謝謝暐翰,最後以fysh711426的方法完成作業!
因為您的建議方式有更新到其他門市的資料,謝謝您大力幫忙

https://ithelp.ithome.com.tw/upload/images/20180625/200111331OZkgkV5Yb.jpg

sjzgo iT邦新手 4 級 ‧ 2018-06-25 17:46:24 檢舉

謝謝fysh711426,您所提供的方式也是OK的。
https://ithelp.ithome.com.tw/upload/images/20180625/20011133nyHjfQr5zs.jpg

sjzgo iT邦新手 4 級 ‧ 2018-06-25 18:12:32 檢舉

不好意思,剛才有發現暐翰大大的方式有多更新了10540筆資料,將其他門市的資料也改到了,最後是用fysh711426的方式再把資料表還原重跑一次,才是正確的

fysh711426 iT邦研究生 5 級 ‧ 2018-06-25 23:53:07 檢舉

感謝您的回饋。 /images/emoticon/emoticon41.gif

sjzgo iT邦新手 4 級 ‧ 2018-06-26 10:40:50 檢舉

fysh711426
這麼晚了還在幫公司賣命@@?

fysh711426 iT邦研究生 5 級 ‧ 2018-06-26 12:44:44 檢舉

哈哈哈,我在家裡回的,睡前看一下有沒有留言。 /images/emoticon/emoticon39.gif

0
cancelpc
iT邦新手 4 級 ‧ 2018-06-22 15:39:57

其實不用這麼複雜,先加上補數在除(取整)
(安全量 + (基本撥量 - 1))/ 2
便會自動進位到最接近的整數。

以商品貨號0011092為例(圖1)
安全量48,基本撥量2:
(48+(2-1))/2 = 24

以商品貨號0015584為例(圖2)
安全量16,基本撥量3:
(16+(3-1))/2 = 9

SELECT a.商品貨號,a.安全量,b.基本撥量,

CAST(((a.安全量 + b.基本撥量-1) / 2) AS int) AS 撥量

FROM 門市安全量基本檔 AS a
RIGHT JOIN 商品基本檔 AS b ON b.商品貨號=a.商品貨號
WHERE (b.大類編號 BETWEEN '1' AND '7') AND a.門市編號='1001' AND b.淘汰='1'

sjzgo iT邦新手 4 級 ‧ 2018-06-22 18:00:08 檢舉

謝謝cancelpc,依照您的建議,商品貨號0015583應為8才符合需求,但實際SELECT出來的結果變成9了,因為16/2=8,符合4的倍數

sjzgo iT邦新手 4 級 ‧ 2018-06-22 18:07:25 檢舉

https://ithelp.ithome.com.tw/upload/images/20180622/20011133LGWsh0p7d8.jpg

我要發表回答

立即登入回答