請協助我有兩個tabel
Tabel A (PO, serial number) =>這個表PO 是空的,需要填對應資料
( , A0009)
( , B0011)
Tabel B (PO, serail start , serial end, QTY) => 這個表有PO 號,每個PO 有對應起始條碼,結束條碼, PO數量
(010 , A0001 , A0010 ,10 ) =>PO為010: 對應的起始條碼是A0001,結束條碼是A0010,此PO共計10個數量
(020 , B0001 , B0100 ,100 )
SQL要如何寫,將兩個表聯合起來,使得表A 對應PO列更新
(010 , A0009)
(020 , B0011)
非常感謝
uj5u.com熱心網友回復：
UPDATE TableASET PO =TabelB.PO
FROM TabelB
WHERE TableA.serial number=TabelB.serial number
uj5u.com熱心網友回復：
你條碼的組成格式是固定的嗎，比如說前綴固定是第一位，后面都是數字。如果不固定，那就比較麻煩，還要判斷第一位數字從哪位開始。下面是固定前綴的處理
IF OBJECT_ID(N'TEMPDB.DBO.#TA') IS NOT NULL
DROP TABLE #TA
GO
CREATE TABLE #TA
(PO VARCHAR(10),
SERIAL_NUMBER VARCHAR(10))
INSERT INTO #TA
SELECT '','A0009' UNION ALL
SELECT '','B0011'
GO
IF OBJECT_ID(N'TEMPDB.DBO.#TB') IS NOT NULL
DROP TABLE #TB
GO
CREATE TABLE #TB
(PO VARCHAR(10),
SERIAL_START VARCHAR(10),
SERIAL_END VARCHAR(10),
QTY INT)
INSERT INTO #TB
SELECT '010','A0001','A0010',10 UNION ALL
SELECT '020','B0001','B0100',100
UPDATE #TA
SET PO=B.PO
FROM
(SELECT *,
LEFT(SERIAL_NUMBER,1) AS PRE_SERIAL,
CAST(SUBSTRING(SERIAL_NUMBER,2,LEN(SERIAL_NUMBER)-1) AS INT) AS SERIAL_NUMBER_NEW
FROM #TA) A
JOIN
(SELECT *,
LEFT(SERIAL_START,1) AS PRE_SERIAL,
CAST(SUBSTRING(SERIAL_START,2,LEN(SERIAL_START)-1) AS INT) AS SERIAL_START_NEW,
CAST(SUBSTRING(SERIAL_END,2,LEN(SERIAL_END)-1) AS INT) AS SERIAL_END_NEW
FROM #TB) AS B ON A.PRE_SERIAL=B.PRE_SERIAL AND A.SERIAL_NUMBER_NEW BETWEEN B.SERIAL_START_NEW AND B.SERIAL_END_NEW
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/200482.html
標籤：基礎類
上一篇：啊啊提問百度不到