--原資料
;WITH TEMP AS
(
SELECT 'WES4300719103000582' SERVICE_SHEET_NO, 'WI-C300' MODEL_CODE, '8020410' SERIAL_NO, 'WI-C310' MOBILE_MODEL_CODE, '0017876'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES1200019103001546' SERVICE_SHEET_NO, 'WI-C300' MODEL_CODE, '8008351' SERIAL_NO, 'WI-C310' MOBILE_MODEL_CODE, '0016674'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES1200019103001550' SERVICE_SHEET_NO, 'WH-CH500' MODEL_CODE, '8017811' SERIAL_NO, 'WH-CH500' MOBILE_MODEL_CODE, '8018930'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES1200719110700081' SERVICE_SHEET_NO, 'NW-WS413' MODEL_CODE, '6073313' SERIAL_NO, 'NW-WS413' MOBILE_MODEL_CODE, '6117484'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES3301119091500140' SERVICE_SHEET_NO, 'WI-C600N' MODEL_CODE, '5061351' SERIAL_NO, 'WI-C600N' MOBILE_MODEL_CODE, '5080268'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES4200419091600102' SERVICE_SHEET_NO, 'WI-1000X' MODEL_CODE, '5383857' SERIAL_NO, 'WI-1000X' MOBILE_MODEL_CODE, '5463800'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES4200419091600102' SERVICE_SHEET_NO, 'WI-1000X' MODEL_CODE, '5463800' SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO UNION ALL
SELECT 'WES2201219101900283' SERVICE_SHEET_NO, 'NW-WS623' MODEL_CODE, '5421453' SERIAL_NO, 'NW-WS623' MOBILE_MODEL_CODE, '5601874'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES2201219101900284' SERVICE_SHEET_NO, 'NW-WS623' MODEL_CODE, '5601874' SERIAL_NO, 'NW-WS623' MOBILE_MODEL_CODE, '5601875'MOBILE_SERIAL_NO UNION ALL
SELECT 'WES2201219101900285' SERVICE_SHEET_NO, 'NW-WS623' MODEL_CODE, '5601875' SERIAL_NO, 'NW-WS623' MOBILE_MODEL_CODE, '5601876'MOBILE_SERIAL_NO UNION ALL
SELECT 'TEST' SERVICE_SHEET_NO, 'TEST-WS623' MODEL_CODE, 'TEST_5421453' SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO
),B AS
(
SELECT SERVICE_SHEET_NO,MODEL_CODE,SERIAL_NO,MOBILE_MODEL_CODE,MOBILE_SERIAL_NO FROM TEMP A
WHERE MOBILE_MODEL_CODE IS NOT NULL AND MOBILE_SERIAL_NO IS NOT NULL
--WHERE A.MODEL_CODE = 'NW-WS623' AND A.SERIAL_NO = '5421453'
UNION ALL
SELECT A.SERVICE_SHEET_NO,A.MODEL_CODE,A.SERIAL_NO,A.MOBILE_MODEL_CODE,A.MOBILE_SERIAL_NO FROM TEMP A
INNER JOIN TEMP B ON A.MODEL_CODE = B.MOBILE_MODEL_CODE AND A.SERIAL_NO = B.MOBILE_SERIAL_NO
WHERE A.MOBILE_MODEL_CODE IS NOT NULL AND A.MOBILE_SERIAL_NO IS NOT NULL
)
SELECT * FROM TEMP
--想要的結果
;WITH TEMP AS
(
SELECT 'WES4300719103000582' SERVICE_SHEET_NO,'WI-C300' MODEL_CODE,'8020410' SERIAL_NO, 'WI-C310' MOBILE_MODEL_CODE,'0017876'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'WES1200019103001546' SERVICE_SHEET_NO,'WI-C300' MODEL_CODE,'8008351' SERIAL_NO, 'WI-C310' MOBILE_MODEL_CODE,'0016674'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'WES1200019103001550' SERVICE_SHEET_NO,'WH-CH500' MODEL_CODE,'8017811' SERIAL_NO,'WH-CH500' MOBILE_MODEL_CODE,'8018930'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'WES1200719110700081' SERVICE_SHEET_NO,'NW-WS413' MODEL_CODE,'6073313' SERIAL_NO,'NW-WS413' MOBILE_MODEL_CODE,'6117484'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'WES3301119091500140' SERVICE_SHEET_NO,'WI-C600N' MODEL_CODE,'5061351' SERIAL_NO,'WI-C600N' MOBILE_MODEL_CODE,'5080268'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'WES4200419091600102' SERVICE_SHEET_NO,'WI-1000X' MODEL_CODE,'5383857' SERIAL_NO,'WI-1000X' MOBILE_MODEL_CODE,'5463800'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'WES4200419091600102' SERVICE_SHEET_NO,'WI-1000X' MODEL_CODE,'5463800' SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO ,0 [COUNT] UNION ALL
SELECT 'WES2201219101900283' SERVICE_SHEET_NO,'NW-WS623' MODEL_CODE,'5421453' SERIAL_NO,'NW-WS623' MOBILE_MODEL_CODE,'5601874'MOBILE_SERIAL_NO ,3 [COUNT] UNION ALL
SELECT 'WES2201219101900284' SERVICE_SHEET_NO,'NW-WS623' MODEL_CODE,'5601874' SERIAL_NO,'NW-WS623' MOBILE_MODEL_CODE,'5601875'MOBILE_SERIAL_NO ,2 [COUNT] UNION ALL
SELECT 'WES2201219101900285' SERVICE_SHEET_NO,'NW-WS623' MODEL_CODE,'5601875' SERIAL_NO,'NW-WS623' MOBILE_MODEL_CODE,'5601876'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
SELECT 'TEST' SERVICE_SHEET_NO, 'TEST-WS623' MODEL_CODE, 'TEST_5421453' SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO ,0 [COUNT]
),B AS
(
SELECT SERVICE_SHEET_NO,MODEL_CODE,SERIAL_NO,MOBILE_MODEL_CODE,MOBILE_SERIAL_NO FROM TEMP A
WHERE MOBILE_MODEL_CODE IS NOT NULL AND MOBILE_SERIAL_NO IS NOT NULL
--WHERE A.MODEL_CODE = 'NW-WS623' AND A.SERIAL_NO = '5421453'
UNION ALL
SELECT A.SERVICE_SHEET_NO,A.MODEL_CODE,A.SERIAL_NO,A.MOBILE_MODEL_CODE,A.MOBILE_SERIAL_NO FROM TEMP A
INNER JOIN TEMP B ON A.MODEL_CODE = B.MOBILE_MODEL_CODE AND A.SERIAL_NO = B.MOBILE_SERIAL_NO
WHERE A.MOBILE_MODEL_CODE IS NOT NULL AND A.MOBILE_SERIAL_NO IS NOT NULL
)
SELECT * FROM TEMP
uj5u.com熱心網友回復：
--MOBILE_MODEL_CODE 與 MOBILE_SERIAL_NO 有值時 對應的 MODEL_CODE與SERIAL_NO 的數量為1，否則為0
--比如 MODEL_CODE = 'NW-WS623' 與 SERIAL_NO = '5421453' 對應的數量是3，
--因為 初始是MODEL_CODE = 'NW-WS623' 與 SERIAL_NO = '5421453' 經過了 3次換機
-- 而 MODEL_CODE = 'WI-1000X' 與 SERIAL_NO = '5383857' 只有1次換機
uj5u.com熱心網友回復：
就是加一列Count?如果是這樣的話用子查詢就可以了，子查詢的條件大概是：
SERVICE_SHEET_NO>=當前記錄的SERVICE_SHEET_NO，MOBILE_MODEL_CODE=當前記錄MODEL_CODE
uj5u.com熱心網友回復：
方便具體說一下嗎？
感謝
uj5u.com熱心網友回復：
又發了一份？另外一個貼里有回復，思路就是用CET先查詢出沒有換過的，再依次查詢換的順序，累計加1即可省略原始資料：
with A AS (SELECT *,
CASE WHEN MOBILE_SERIAL_NO IS NULL THEN 0 ELSE 1 END AS [Count]
FROM TEMP t1
WHERE NOT EXISTS (SELECT 1 FROM TEMP t2 WHERE t1.MOBILE_SERIAL_NO = t2.SERIAL_NO)
UNION ALL
SELECT t3.*,t4.[Count]+1
FROM TEMP t3
INNER JOIN A t4 ON t3.MODEL_CODE = t4.MODEL_CODE
AND t3.MOBILE_SERIAL_NO = t4.SERIAL_NO)
SELECT * FROM A ORDER BY A.MODEL_CODE,A.SERVICE_SHEET_NO,A.[Count] DESC
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/38005.html
標籤：應用實例
上一篇：sql 統計數量
下一篇：JAVA