------ SQL Code ------
CREATE TABLE #TMP
(
RKEY INT,
型号 VARCHAR(20),
版本 VARCHAR(2)
)INSERT INTO #TMP SELECT 861,'P01AL005506','4A'
UNION ALL SELECT 6648,'P01AL005506','0A'
UNION ALL SELECT 14207,'P01AL005506','0B'
UNION ALL SELECT 13616,'P01AL005508','0A'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32388,'P02F4025379','00'
UNION ALL SELECT 32388,'P02F4025379','00'
SELECT * FROM #TMP--目的:取最新型号
--最新型号判断方法:根据SUBSTRING(型号,2,LEN(型号))判断属于同一个型号,如 'S02F4025379','P02F4025379' 'P'开头的型号 比'S'开头的型号要新
--然后根据版本 Right(版本,1)字母越后型号越新,Left(版本,1)数字越大型号越新
--比如:版本 '0B' 要比 '0A' 所对应的型号要新
--比如:版本 '0B' 要比 '4A' 所对应的型号要新//如上数据,取到下图标记的数据
CREATE TABLE #TMP
(
RKEY INT,
型号 VARCHAR(20),
版本 VARCHAR(2)
)INSERT INTO #TMP SELECT 861,'P01AL005506','4A'
UNION ALL SELECT 6648,'P01AL005506','0A'
UNION ALL SELECT 14207,'P01AL005506','0B'
UNION ALL SELECT 13616,'P01AL005508','0A'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32388,'P02F4025379','00'
UNION ALL SELECT 32388,'P02F4025379','00' SELECT RKEY,型号,版本
FROM
(SELECT *,ROW_NUMBER()OVER(PARTITION BY SUBSTRING(型号,2,LEN(型号)) ORDER BY CASE LEFT(型号,1) WHEN 'S' THEN 0 ELSE 1 END DESC)AS row FROM #TMP
)t
WHERE row=1/*
RKEY 型号 版本
861 P01AL005506 4A
13616 P01AL005508 0A
32433 P02F4025379 0A
*/
SELECT RKEY,型号,版本
FROM
(SELECT *,ROW_NUMBER()OVER(PARTITION BY SUBSTRING(型号,2,LEN(型号)) ORDER BY CASE LEFT(型号,1) WHEN 'S' THEN 0 ELSE 1 END DESC,
Right(版本,1) desc
)AS row FROM #TMP
)t
WHERE row=1/*
RKEY 型号 版本
14207 P01AL005506 0B
13616 P01AL005508 0A
32433 P02F4025379 0A
*/
(
RKEY INT,
型号 VARCHAR(20),
版本 VARCHAR(2)
)INSERT INTO #TMP SELECT 861,'P01AL005506','4A'
UNION ALL SELECT 6648,'P01AL005506','0A'
UNION ALL SELECT 14207,'P01AL005506','0B'
UNION ALL SELECT 13616,'P01AL005508','0A'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32388,'P02F4025379','00'
UNION ALL SELECT 32388,'P02F4025379','00'
SELECT * FROM #TMP T WHERE Right(版本,1)=(SELECT MAX(Right(版本,1)) FROM #TMP WHERE SUBSTRING(型号,2,LEN(型号))=SUBSTRING(T.型号,2,LEN(T.型号)))
/*RKEY 型号 版本
----------- -------------------- ----
14207 P01AL005506 0B
13616 P01AL005508 0A
32132 S02F4025379 0B
32132 S02F4025379 0B(4 行受影响)
*/DROP TABLE #TMP
(
RKEY INT,
型号 VARCHAR(20),
版本 VARCHAR(2)
)
INSERT INTO #TMP SELECT 861,'P01AL005506','4A'
UNION ALL SELECT 6648,'P01AL005506','0A'
UNION ALL SELECT 14207,'P01AL005506','0B'
UNION ALL SELECT 13616,'P01AL005508','0A'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32388,'P02F4025379','00'
UNION ALL SELECT 32388,'P02F4025379','00'
--目的:取最新型号
--最新型号判断方法:根据SUBSTRING(型号,2,LEN(型号))判断属于同一个型号,
--如 'S02F4025379','P02F4025379' 'P'开头的型号 比'S'开头的型号要新
--然后根据版本 Right(版本,1)字母越后型号越新,Left(版本,1)数字越大型号越新
--比如:版本 '0B' 要比 '0A' 所对应的型号要新
--比如:版本 '0B' 要比 '4A' 所对应的型号要新
go
select * from #tmp a where not exists(
select 1 from #tmp where substring(型号,2,len(型号)-1)=substring(a.型号,2,len(a.型号)-1)
and (型号<a.型号 or 版本>a.版本))
/*
RKEY 型号 版本
----------- -------------------- ----
861 P01AL005506 4A
13616 P01AL005508 0A(2 行受影响)*/
FROM
(SELECT *,ROW_NUMBER()OVER(PARTITION BY SUBSTRING(型号,2,LEN(型号)) ORDER BY CASE LEFT(型号,1) WHEN 'S' THEN 0 ELSE 1 END DESC,
Right(版本,1) DESC,LEFT(版本,1)desc
)AS row FROM #TMP
)t
WHERE row=1
(
RKEY INT,
型号 VARCHAR(20),
版本 VARCHAR(2)
)
INSERT INTO #TMP SELECT 861,'P01AL005506','4A'
UNION ALL SELECT 6648,'P01AL005506','0A'
UNION ALL SELECT 14207,'P01AL005506','0B'
UNION ALL SELECT 13616,'P01AL005508','0A'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31746,'S02F4025379','00'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 31850,'S02F4025379','0A'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32132,'S02F4025379','0B'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32433,'P02F4025379','0A'
UNION ALL SELECT 32388,'P02F4025379','00'
UNION ALL SELECT 32388,'P02F4025379','00'
--目的:取最新型号
--最新型号判断方法:根据SUBSTRING(型号,2,LEN(型号))判断属于同一个型号,
--如 'S02F4025379','P02F4025379' 'P'开头的型号 比'S'开头的型号要新
--然后根据版本 Right(版本,1)字母越后型号越新,Left(版本,1)数字越大型号越新
--比如:版本 '0B' 要比 '0A' 所对应的型号要新
--比如:版本 '0B' 要比 '4A' 所对应的型号要新
goselect distinct * from #tmp a where not exists(
select 1 from #tmp where substring(型号,2,len(型号))=substring(a.型号,2,len(a.型号))
and left(型号,1)='P' and left(a.型号,1)='S' or 型号=a.型号 and 版本>a.版本)
/*
RKEY 型号 版本
----------- -------------------- ----
861 P01AL005506 4A
13616 P01AL005508 0A
32433 P02F4025379 0A(3 行受影响)*/
go
drop table #tmp
嗯,没问题了。 多谢~~
我在比较这几种SQL