CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000)) --将max改为4000了。 INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,5' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,2,null UNION ALL SELECT 5,2,'2,8' UNION ALL SELECT 6,2,'0,12|2,13' UNION ALL SELECT 7,2,'0,14|2,15|5,11' UNION ALL SELECT 8,2,'0,17|3,13|2,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,16|20,28'/**/ go;with ach as ( select a.pkID,a.CateID,substring(a.SortID,b.number,charindex('|',a.SortID+'|',b.number)-b.number) SortID from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.SortID) and substring('|'+a.SortID,b.number,1) = '|' )select a.* from tb a left join ach b on a.pkID = b.pkID and b.SortID like '2,%' where a.CateID = 2 order by (case when b.pkID is null then 1 else 0 end), convert(int,right(b.SortID,len(b.SortID)-charindex(',',b.SortID))) desc,a.pkIDdrop table tb/*****************pkID CateID SortID ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 4 2 NULL 10 2 0,7|6,19(10 行受影响)
DECLARE @CateID INT; SET @CateID = 2 SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid /* pkID CateID SortID ----------- ----------- ------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 4 2 NULL 10 2 0,7|6,19 */
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,5' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,2,null UNION ALL SELECT 5,2,'2,8' UNION ALL SELECT 6,2,'0,12|2,13' UNION ALL SELECT 7,2,'0,14|2,15|5,11' UNION ALL SELECT 8,2,'0,17|3,13|2,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,16|20,28'/**/ go select * from tb order by (case when charindex('2,',sortid)>0 then substring(sortid,charindex('2,',sortid),charindex('|',sortid+'|',charindex('2,',sortid))-charindex('2,',sortid)) else sortid end) /* pkID CateID SortID ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 4 2 NULL 3 3 1 5 0,2 10 2 0,7|6,19 11 2 2,11|6,20 6 2 0,12|2,13 7 2 0,14|2,15|5,11 12 2 2,16|20,28 8 2 0,17|3,13|2,18 9 2 2,2|12,17|12,156 2 2 2,5 5 2 2,8(12 行受影响)*/ go drop table tb
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,5' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,2,null UNION ALL SELECT 5,2,'2,8' UNION ALL SELECT 6,2,'0,12|2,13' UNION ALL SELECT 7,2,'0,14|2,15|5,11' UNION ALL SELECT 8,2,'0,17|3,13|2,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,16|20,28'/**/ go select * from tb order by (case when charindex('2,',sortid)>0 then convert(int,substring(sortid,charindex('2,',sortid)+2,charindex('|',sortid+'|',charindex('2,',sortid))-charindex('2,',sortid)-2)) else 0 end)desc,pkid desc /* pkID CateID SortID ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 10 2 0,7|6,19 4 2 NULL 3 3 1 5 0,2(12 行受影响)*/ go drop table tb
--4楼改进,如果一列有多个 2,n 按 n 的最大值倒序排列:;with ach as ( select a.pkID,a.CateID,substring(a.SortID,b.number,charindex('|',a.SortID+'|',b.number)-b.number) SortID from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.SortID) and substring('|'+a.SortID,b.number,1) = '|' )select a.* from tb a left join ach b on a.pkID = b.pkID and b.SortID like '2,%' where a.CateID = 2 group by a.pkID,a.CateID,a.SortID,b.pkID order by (case when b.pkID is null then 1 else 0 end), max(convert(int,right(b.SortID,len(b.SortID)-charindex(',',b.SortID)))) desc,a.pkID
select * from tb where CateID=2 order by case when charindex('2,',SortID)>0 then cast(substring(SortID+',',charindex('2,',SortID)+2, charindex(',',replace(SortID,'|',',')+',', charindex('2,',SortID)+2)-charindex('2,',SortID)-2) as int) else 0 end desc,pkID /* pkID CateID SortID ----------------------------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 4 2 NULL 10 2 0,7|6,19(所影响的行数为 10 行) */
这样再试试 select * from tb where CateID=2 order by case when charindex('|2,','|'+SortID+'|')>0 then cast(substring(SortID+'|',charindex('|2,','|'+SortID+'|')+2, charindex('|',SortID+'|', charindex('|2,','|'+SortID+'|')+2)-charindex('|2,','|'+SortID)-2) as int) else 0 end desc,pkID
“豆子”,已经解决了这个问题,加上类似 2,123 2,12345 这样的,结果也正确。 我整理了一下,才知道,原来是这么回事: IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,6,'2,5' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,6,'6,8' UNION ALL SELECT 6,6,'0,12|6,13' UNION ALL SELECT 7,6,'6,15|66,15|5,11' UNION ALL SELECT 8,6,'0,17|6,16|22,18' UNION ALL SELECT 9,6,'2,2|12,17|12,156' UNION ALL SELECT 10,6,'0,7|6,19' UNION ALL SELECT 11,6,'2,11|6,20' UNION ALL SELECT 12,6,'2,123|2,12345' UNION ALL SELECT 13,6,'2,16|20,28' go DECLARE @CateID INT; SET @CateID = 6;SELECT *, CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + 2, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + 2 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 ) AS INT ) ELSE 0 END AS NewSortID FROM tb WHERE CateID=@CateID ORDER BY CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + 2, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + 2 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 ) AS INT ) ELSE 0 END DESC ,pkID DESC
问题出在这里,当我把: DECLARE @CateID INT; SET @CateID = 20; 改为: DECLARE @CateID INT; SET @CateID = 20; 时,就出现了错误提示,是怎么回事?消息 245,级别 16,状态 1,第 2 行 在将 nvarchar 值 ',28' 转换成数据类型 int 时失败。IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,1' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,2,'6,8' UNION ALL SELECT 6,2,'0,12|6,13' UNION ALL SELECT 7,5,'6,15|66,15|5,11' UNION ALL SELECT 8,2,'0,17|6,16|22,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,123|2,12345' UNION ALL SELECT 13,2,'2,16|20,28' go DECLARE @CateID INT; SET @CateID = 20; SELECT *, CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + 2, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + 2 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 ) AS INT ) ELSE 0 END AS NewSortID FROM tb
问题出在这里,当我把: DECLARE @CateID INT; SET @CateID = 2; 改为: DECLARE @CateID INT; SET @CateID = 20; 时,就出现了错误提示,是怎么回事? 消息 245,级别 16,状态 1,第 2 行 在将 nvarchar 值 ',28' 转换成数据类型 int 时失败。
改好了,看下面 IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000)) INSERT INTO [tb] SELECT 9,15,null UNION ALL SELECT 16,20,'20,1|2,6' UNION ALL SELECT 30,20,'5,8|20,3' UNION ALL SELECT 32,20,'|6,12|20,4|9,9' UNION ALL SELECT 35,15,null DECLARE @CateID INT; SET @CateID = 20;SELECT *, CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 -len(RTRIM(@CateID)) ) AS INT ) ELSE 0 END AS NewSortID FROM tb WHERE CateID=@CateID ORDER BY CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 -len(RTRIM(@CateID)) ) AS INT ) ELSE 0 END DESC ,pkID DESC
没有出错信息了,但是结果却不正确了,如下: IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,1' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,2,'6,8' UNION ALL SELECT 6,2,'0,12|6,13' UNION ALL SELECT 7,5,'6,15|66,15|5,11' UNION ALL SELECT 8,2,'0,17|6,16|22,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,123|2,12345' UNION ALL SELECT 13,2,'2,16|20,28'DECLARE @CateID INT; SET @CateID = 20;SELECT *, CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 -len(RTRIM(@CateID)) ) AS INT ) ELSE 0 END AS NewSortID FROM tb ORDER BY CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 2 -len(RTRIM(@CateID)) ) AS INT ) ELSE 0 END DESC ,pkID DESC --结果是: 13 2 2,16|20,28 2 12 2 2,123|2,12345 0 11 2 2,11|6,20 0 10 2 0,7|6,19 0 9 2 2,2|12,17|12,156 0 8 2 0,17|6,16|22,18 0 7 5 6,15|66,15|5,11 0 6 2 0,12|6,13 0 5 2 6,8 0 4 6 NULL 0 3 3 0 2 2 2,1 0 1 5 0,2 0 --最后那行NewSortID也没有正确生成.谢谢豆子. --
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,1' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,2,'6,8' UNION ALL SELECT 6,2,'0,12|6,13' UNION ALL SELECT 7,5,'6,15|66,15|5,11' UNION ALL SELECT 8,2,'0,17|6,16|22,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,123|2,12345' UNION ALL SELECT 13,2,'2,16|20,28'DECLARE @CateID INT; SET @CateID = 20;SELECT *, (case when isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)+2,2),'|','') end) as newsortid FROM [tb] order by (case when isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)+2,2),'|','') end) desc,pkid 王兄,你的代码一样存在这个问题,谢谢.
最后那行NewSortID也没有正确生成,是指下面这行吗?1 5 0,2 0
DECLARE @CateID INT; SET @CateID = 2 --可以改成20SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|'+ltrim(@CateID)+',','|'+ [SortID]),0)+len(@CateID)+1,len(@CateID)+1),'|','') end) desc,pkid
--叶子,执行后没数据了,还需要改一下 IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,1' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,2,'6,8' UNION ALL SELECT 6,2,'0,12|6,13' UNION ALL SELECT 7,5,'6,15|66,15|5,11' UNION ALL SELECT 8,2,'0,17|6,16|22,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,123|2,12345' UNION ALL SELECT 13,2,'2,16|20,28'DECLARE @CateID INT; SET @CateID = 20 --可以改成20SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|'+ltrim(@CateID)+',','|'+ [SortID]),0)+len(@CateID)+1,len(@CateID)+1),'|','') end) desc,pkid
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000)) insert into [tb] select 1,5,'0,2' union all select 2,6,'2,5' union all select 3,3,'' union all select 4,6,null union all select 5,6,'6,8' union all select 6,6,'0,12|6,13' union all select 7,6,'6,15|66,15|5,11' union all select 8,6,'0,17|6,16|22,18' union all select 9,6,'2,2|12,17|12,156' union all select 10,6,'0,7|6,19' union all select 11,6,'2,11|6,20' union all select 12,6,'2,123|2,12345' union all select 13,6,'2,16|20,28' godeclare @cateid int set @cateid = 6select * from tb where cateid = @cateid order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)), charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))- (charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid descdrop table tb/**************pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 11 6 2,11|6,20 10 6 0,7|6,19 8 6 0,17|6,16|22,18 7 6 6,15|66,15|5,11 6 6 0,12|6,13 5 6 6,8 13 6 2,16|20,28 12 6 2,123|2,12345 9 6 2,2|12,17|12,156 4 6 NULL 2 6 2,5(11 行受影响)
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000)) insert into [tb] select 1,5,'0,2' union all select 2,2,'2,1' union all select 3,3,'' union all select 4,6,null union all select 5,2,'6,8' union all select 6,2,'0,12|6,13' union all select 7,5,'6,15|66,15|5,11' union all select 8,2,'0,17|6,16|22,18' union all select 9,2,'2,2|12,17|12,156' union all select 10,2,'0,7|6,19' union all select 11,2,'2,11|6,20' union all select 12,2,'2,123|2,12345' union all select 13,2,'2,16|20,28' godeclare @cateid int set @cateid = 2select * from tb where cateid = @cateid order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)), charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))- (charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid desc/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 2 2,123|2,12345 13 2 2,16|20,28 11 2 2,11|6,20 9 2 2,2|12,17|12,156 2 2 2,1 10 2 0,7|6,19 8 2 0,17|6,16|22,18 6 2 0,12|6,13 5 2 6,8(9 行受影响) */select * from [tb] where cateid=@cateid order by (case when isnull(charindex('|'+ltrim(@cateid)+',','|'+[sortid]),0)=0 then 0 else replace(substring([sortid],isnull(charindex('|'+ltrim(@cateid)+',','|'+ [sortid]),0)+len(@cateid)+1,len(@cateid)+1),'|','') end) desc,pkid/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 13 2 2,16|20,28 12 2 2,123|2,12345 11 2 2,11|6,20 9 2 2,2|12,17|12,156 2 2 2,1 5 2 6,8 6 2 0,12|6,13 8 2 0,17|6,16|22,18 10 2 0,7|6,19(9 行受影响) */drop table tb
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,1' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,2,'6,8' UNION ALL SELECT 6,2,'0,12|6,13' UNION ALL SELECT 7,5,'6,15|66,15|5,11' UNION ALL SELECT 8,2,'0,17|6,16|22,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,123|2,12345' UNION ALL SELECT 13,2,'2,16|20,28' union all select 14,20,'20,19' union all select 15,20,'2,12|20,18|9,1' union all select 16,20,'2,1|8,9|20,20' DECLARE @CateID INT; SET @CateID = 20 SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid /* pkID CateID SortID ----------- ----------- ------------------ 15 20 2,12|20,18|9,1 16 20 2,1|8,9|20,20 14 20 20,19 */ 刚才没有数据是因为测试数据中没有CateID=20的数据。
不能直接截取len(@cateid)+1这么点
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000)) insert into [tb] select 1,5,'0,2' union all select 2,2,'2,1' union all select 3,3,'' union all select 4,6,null union all select 5,2,'6,8' union all select 6,2,'0,12|6,13' union all select 7,5,'6,15|66,15|5,11' union all select 8,2,'0,17|6,16|22,18' union all select 9,2,'2,2|12,17|12,156' union all select 10,2,'0,7|6,19' union all select 11,2,'2,11|6,20' union all select 12,2,'2,123|2,12345' union all select 13,2,'2,16|20,28' union all select 14,20,'20,19' union all select 15,20,'2,12|20,18|9,1' union all select 16,20,'2,1|8,9|20,20' godeclare @cateid int set @cateid = 2--me : select * from tb where cateid = @cateid order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)), charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))- (charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid desc/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 2 2,123|2,12345 13 2 2,16|20,28 11 2 2,11|6,20 9 2 2,2|12,17|12,156 2 2 2,1 10 2 0,7|6,19 8 2 0,17|6,16|22,18 6 2 0,12|6,13 5 2 6,8(9 行受影响) */--豆子: select * from [tb] where cateid=@cateid order by (case when isnull(charindex('|'+ltrim(@cateid)+',','|'+[sortid]),0)=0 then 0 else replace(substring([sortid],isnull(charindex('|'+ltrim(@cateid)+',','|'+ [sortid]),0)+len(@cateid)+1,len(@cateid)+1),'|','') end) desc,pkid/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 13 2 2,16|20,28 12 2 2,123|2,12345 11 2 2,11|6,20 9 2 2,2|12,17|12,156 2 2 2,1 5 2 6,8 6 2 0,12|6,13 8 2 0,17|6,16|22,18 10 2 0,7|6,19(9 行受影响) */--老王大哥: select * from [tb] where cateid=@cateid order by (case when isnull(charindex('|2,','|'+[sortid]),0)=0 then 0 else replace(substring([sortid],isnull(charindex('|2,','|'+[sortid]),0)+2,2),'|','') end) desc,pkid/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 13 2 2,16|20,28 12 2 2,123|2,12345 11 2 2,11|6,20 9 2 2,2|12,17|12,156 2 2 2,1 5 2 6,8 6 2 0,12|6,13 8 2 0,17|6,16|22,18 10 2 0,7|6,19(9 行受影响) */drop table tb
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] GO CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,1' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,6,null UNION ALL SELECT 5,2,'6,8' UNION ALL SELECT 6,2,'0,12|6,13' UNION ALL SELECT 7,5,'6,15|66,15|5,11' UNION ALL SELECT 8,2,'0,17|6,16|22,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,123|2,12345' UNION ALL SELECT 13,2,'2,16|20,28' union all select 14,20,'20,19' union all select 15,20,'2,12|20,18|9,1' union all select 16,20,'2,1|8,9|20,20'DECLARE @CateID INT; SET @CateID = 20 SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|'+ltrim(@CateID)+',','|'+ [SortID]),0)+len(@CateID)+1,len(@CateID)+1),'|','') end) desc,pkid/* pkID CateID SortID ----------- ----------- ----------------- 16 20 2,1|8,9|20,20 14 20 20,19 15 20 2,12|20,18|9,1 */刚才发的是以前的,33楼的应该没有问题。
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000)) insert into [tb] select 1,5,'0,2' union all select 2,2,'200,1' union all select 3,3,'' union all select 4,6,null union all select 5,200,'6,8' union all select 6,200,'0,12|6,13' union all select 7,5,'6,15|66,15|5,11' union all select 8,200,'0,17|6,16|22,18' union all select 9,200,'200,2|12,17|12,156' union all select 10,200,'0,7|6,19' union all select 11,200,'2,11|6,20' union all select 12,200,'2,123|200,12345' union all select 13,200,'2,16|200,28' union all select 14,200,'20,19' union all select 15,200,'2,12|200,18|9,1' union all select 16,200,'2,1|8,9|200,20' godeclare @cateid int set @cateid = 200--me : select * from tb where cateid = @cateid order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)), charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))- (charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid desc/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 200 2,123|200,12345 13 200 2,16|200,28 16 200 2,1|8,9|200,20 15 200 2,12|200,18|9,1 9 200 200,2|12,17|12,156 14 200 20,19 11 200 2,11|6,20 10 200 0,7|6,19 8 200 0,17|6,16|22,18 6 200 0,12|6,13 5 200 6,8(11 行受影响) */--豆子: select * from [tb] where cateid=@cateid order by (case when isnull(charindex('|'+ltrim(@cateid)+',','|'+[sortid]),0)=0 then 0 else replace(substring([sortid],isnull(charindex('|'+ltrim(@cateid)+',','|'+ [sortid]),0)+len(@cateid)+1,len(@cateid)+1),'|','') end) desc,pkid/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 200 2,123|200,12345 --200,12345 9 200 200,2|12,17|12,156 --200,2 15 200 2,12|200,18|9,1 --200,18 13 200 2,16|200,28 --200,28 16 200 2,1|8,9|200,20 --20020 5 200 6,8 6 200 0,12|6,13 8 200 0,17|6,16|22,18 10 200 0,7|6,19 11 200 2,11|6,20 14 200 20,19(11 行受影响) */--老王大哥: select * from [tb] where cateid=@cateid order by (case when isnull(charindex('|2,','|'+[sortid]),0)=0 then 0 else replace(substring([sortid],isnull(charindex('|2,','|'+[sortid]),0)+2,2),'|','') end) desc,pkid/* pkid cateid sortid ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 13 200 2,16|200,28 --200,28 12 200 2,123|200,12345 15 200 2,12|200,18|9,1 --200,18 11 200 2,11|6,20 --没有200 16 200 2,1|8,9|200,20 --200,20 5 200 6,8 6 200 0,12|6,13 8 200 0,17|6,16|22,18 9 200 200,2|12,17|12,156 10 200 0,7|6,19 14 200 20,19(11 行受影响) */drop table tb 。。
这样就没问题了create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000)) insert into [tb] select 1,5,'0,2' union all select 2,2,'200,1' union all select 3,3,'' union all select 4,6,null union all select 5,200,'6,8' union all select 6,200,'0,12|6,13' union all select 7,5,'6,15|66,15|5,11' union all select 8,200,'0,17|6,16|22,18' union all select 9,200,'200,2|12,17|12,156' union all select 10,200,'0,7|6,19' union all select 11,200,'2,11|6,20' union all select 12,200,'2,123|200,12345' union all select 13,200,'2,16|200,28' union all select 14,200,'20,19' union all select 15,200,'2,12|200,18|9,1' union all select 16,200,'2,1|8,9|200,20' goDECLARE @CateID INT; SET @CateID = 200;SELECT *, CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 1 -len(RTRIM(@CateID)) ) AS INT ) ELSE 0 END as NewSortID FROM tb WHERE CateID=@CateID ORDER BY CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0 THEN CAST ( SUBSTRING ( SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1, CHARINDEX ( '|', SortID + '|', CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID + '|' ) + len(RTRIM(@CateID))+1 ) - CHARINDEX ( '|' + RTRIM(@CateID) + ',', '|' + SortID ) - 1 -len(RTRIM(@CateID)) ) AS INT ) ELSE 0 END DESC ,pkID DESC /* pkid cateid sortid NewSortID ----------- ----------- ------------------------------- 12 200 2,123|200,12345 12345 13 200 2,16|200,28 28 16 200 2,1|8,9|200,20 20 15 200 2,12|200,18|9,1 18 9 200 200,2|12,17|12,156 2 14 200 20,19 0 11 200 2,11|6,20 0 10 200 0,7|6,19 0 8 200 0,17|6,16|22,18 0 5 200 6,8 0(所影响的行数为 11 行)*/
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb] go create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](20)) insert into [tb] select 1,5,'0,2' union all select 2,2,'200,1' union all select 3,3,'' union all select 4,6,null union all select 5,200,'6,8' union all select 6,200,'0,12|6,13' union all select 7,5,'6,15|66,15|5,11' union all select 8,200,'0,17|6,16|22,18' union all select 9,200,'200,2|12,17|12,156' union all select 10,200,'0,7|6,19' union all select 11,200,'2,11|6,20' union all select 12,200,'2,123|200,12345' union all select 13,200,'2,16|200,28' union all select 14,200,'20,19' union all select 15,200,'2,12|200,18|9,1' union all select 16,200,'2,1|8,9|200,20' gogo declare @cateid int;set @cateid = 200select * from tb where cateid=@cateid order by case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0 else left(right([SortID]+'|',len([SortID]+'|')- charindex('|'+ltrim(@CateID)+',','|'+[SortID])-len(@CateID)), charindex('|',right([SortID]+'|',len([SortID]+'|')- charindex('|'+ltrim(@CateID)+',','|'+[SortID])-len(@CateID)) )-1) end desc,pkid /* pkid cateid sortid ----------- ----------- -------------------- 12 200 2,123|200,12345 13 200 2,16|200,28 16 200 2,1|8,9|200,20 15 200 2,12|200,18|9,1 9 200 200,2|12,17|12,156 5 200 6,8 6 200 0,12|6,13 8 200 0,17|6,16|22,18 10 200 0,7|6,19 11 200 2,11|6,20 14 200 20,19 */
SQL code DECLARE @CateID INT; SET @CateID = 2 SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid /* pkID CateID SortID ----------- ----------- ------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 4 2 NULL 10 2 0,7|6,19 */
SQL code DECLARE @CateID INT; SET @CateID = 2 SELECT * FROM [tb] WHERE CateID=@CateID order by (case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0 else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid /* pkID CateID SortID ----------- ----------- ------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 4 2 NULL 10 2 0,7|6,19 */
一个小小的排序,弄那么复杂啊... 如果存在类似于 22,5 之类的,如((如果有其他条件,则加进去就是了): CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max)) INSERT INTO [tb] SELECT 1,5,'0,2' UNION ALL SELECT 2,2,'2,5' UNION ALL SELECT 3,3,'' UNION ALL SELECT 4,2,null UNION ALL SELECT 5,2,'2,8' UNION ALL SELECT 6,2,'0,12|2,13' UNION ALL SELECT 7,2,'0,14|2,15|5,11' UNION ALL SELECT 8,2,'0,17|3,13|2,18' UNION ALL SELECT 9,2,'2,2|12,17|12,156' UNION ALL SELECT 10,2,'0,7|6,19' UNION ALL SELECT 11,2,'2,11|6,20' UNION ALL SELECT 12,2,'2,16|20,28' UNION ALL select 13,2,'22,20' UNION ALL --这两行是添加的含 22,xx 的 select 14,2,'0,25|22,2' go select * from tb order by (case when charindex('|2,','|'+sortid)>0 then substring(sortid, charindex('|2,','|'+sortid)+2, charindex('|',sortid+'|',charindex('|2,','|'+sortid)+2)-charindex('|2,','|'+sortid)-2) else 0 end) desc,pkid desc /* pkID CateID SortID ----------- ----------- ----------------------------------------------------- 8 2 0,17|3,13|2,18 12 2 2,16|20,28 7 2 0,14|2,15|5,11 6 2 0,12|2,13 11 2 2,11|6,20 5 2 2,8 2 2 2,5 9 2 2,2|12,17|12,156 14 2 0,25|22,2 13 2 22,20 10 2 0,7|6,19 4 2 NULL 3 3 1 5 0,2(14 行受影响) */ go drop table tb
1 5 0,2
2 2 2,5
3 3
4 2 NULL
5 2 2,8
6 2 0,12|2,13
7 2 0,14|2,15|5,11
8 2 0,17|3,13|2,18
9 2 2,2|12,17|12,156
10 2 0,7|6,19
11 2 2,11|6,20
12 2 2,16|20,28DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb]-- WHERE CateID=@CateID//加了条件后,结果如下:
2 2 2,5
4 2 NULL
5 2 2,8
6 2 0,12|2,13
7 2 0,14|2,15|5,11
8 2 0,17|3,13|2,18
9 2 2,2|12,17|12,156
10 2 0,7|6,19
11 2 2,11|6,20
12 2 2,16|20,28DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb] WHERE CateID=@CateID ORDER BY ……
//我需要的结果,逆序排结果如下,顺序排,顺序反过来,可自定义:
8 2 0,17|3,13|2,18 //依据中间的:2,18
12 2 2,16|20,28 //依据中间的:2,16
7 2 0,14|2,15|5,11 //依据中间的:2,15
6 2 0,12|2,13 //依据中间的:2,13
11 2 2,11|6,20 //依据中间的:2,11
5 2 2,8 //依据中间的:2,8
2 2 2,5 //依据中间的:2,5
9 2 2,2|12,17|12,156 //依据中间的:2,2
4 2 NULL //没有2,n的,按照pkID顺序或逆序排(可自定)
10 2 0,7|6,19 //没有2,n的,按照pkID顺序或逆序排(可自定)
1 5 0,2
2 2 2,5
3 3
4 2 NULL
5 2 2,8
6 2 0,12|2,13
7 2 0,14|2,15|5,11
8 2 0,17|3,13|2,18
9 2 2,2|12,17|12,156
10 2 0,7|6,19
11 2 2,11|6,20
12 2 2,16|20,28DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb]-- WHERE CateID=@CateID//加了条件后,结果如下:
2 2 2,5
4 2 NULL
5 2 2,8
6 2 0,12|2,13
7 2 0,14|2,15|5,11
8 2 0,17|3,13|2,18
9 2 2,2|12,17|12,156
10 2 0,7|6,19
11 2 2,11|6,20
12 2 2,16|20,28DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb] WHERE CateID=@CateID ORDER BY ……
//我需要的结果,逆序排结果如下,顺序排,顺序反过来,可自定义:
8 2 0,17|3,13|2,18 //依据中间的:2,18
12 2 2,16|20,28 //依据中间的:2,16
7 2 0,14|2,15|5,11 //依据中间的:2,15
6 2 0,12|2,13 //依据中间的:2,13
11 2 2,11|6,20 //依据中间的:2,11
5 2 2,8 //依据中间的:2,8
2 2 2,5 //依据中间的:2,5
9 2 2,2|12,17|12,156 //依据中间的:2,2
4 2 NULL //没有2,n的,按照pkID顺序或逆序排(可自定)
10 2 0,7|6,19 //没有2,n的,按照pkID顺序或逆序排(可自定)
上面的格式乱了,看不清楚。
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000)) --将max改为4000了。
INSERT INTO [tb]
SELECT 1,5,'0,2' UNION ALL
SELECT 2,2,'2,5' UNION ALL
SELECT 3,3,'' UNION ALL
SELECT 4,2,null UNION ALL
SELECT 5,2,'2,8' UNION ALL
SELECT 6,2,'0,12|2,13' UNION ALL
SELECT 7,2,'0,14|2,15|5,11' UNION ALL
SELECT 8,2,'0,17|3,13|2,18' UNION ALL
SELECT 9,2,'2,2|12,17|12,156' UNION ALL
SELECT 10,2,'0,7|6,19' UNION ALL
SELECT 11,2,'2,11|6,20' UNION ALL
SELECT 12,2,'2,16|20,28'/**/
go;with ach as
(
select a.pkID,a.CateID,substring(a.SortID,b.number,charindex('|',a.SortID+'|',b.number)-b.number) SortID
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.SortID)
and substring('|'+a.SortID,b.number,1) = '|'
)select a.*
from tb a left join ach b on a.pkID = b.pkID and b.SortID like '2,%'
where a.CateID = 2
order by (case when b.pkID is null then 1 else 0 end),
convert(int,right(b.SortID,len(b.SortID)-charindex(',',b.SortID))) desc,a.pkIDdrop table tb/*****************pkID CateID SortID
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
4 2 NULL
10 2 0,7|6,19(10 行受影响)
DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid
/*
pkID CateID SortID
----------- ----------- -------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
4 2 NULL
10 2 0,7|6,19
*/
INSERT INTO [tb]
SELECT 1,5,'0,2' UNION ALL
SELECT 2,2,'2,5' UNION ALL
SELECT 3,3,'' UNION ALL
SELECT 4,2,null UNION ALL
SELECT 5,2,'2,8' UNION ALL
SELECT 6,2,'0,12|2,13' UNION ALL
SELECT 7,2,'0,14|2,15|5,11' UNION ALL
SELECT 8,2,'0,17|3,13|2,18' UNION ALL
SELECT 9,2,'2,2|12,17|12,156' UNION ALL
SELECT 10,2,'0,7|6,19' UNION ALL
SELECT 11,2,'2,11|6,20' UNION ALL
SELECT 12,2,'2,16|20,28'/**/
go
select * from tb
order by (case when charindex('2,',sortid)>0 then substring(sortid,charindex('2,',sortid),charindex('|',sortid+'|',charindex('2,',sortid))-charindex('2,',sortid)) else sortid end)
/*
pkID CateID SortID
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 2 NULL
3 3
1 5 0,2
10 2 0,7|6,19
11 2 2,11|6,20
6 2 0,12|2,13
7 2 0,14|2,15|5,11
12 2 2,16|20,28
8 2 0,17|3,13|2,18
9 2 2,2|12,17|12,156
2 2 2,5
5 2 2,8(12 行受影响)*/
go
drop table tb
INSERT INTO [tb]
SELECT 1,5,'0,2' UNION ALL
SELECT 2,2,'2,5' UNION ALL
SELECT 3,3,'' UNION ALL
SELECT 4,2,null UNION ALL
SELECT 5,2,'2,8' UNION ALL
SELECT 6,2,'0,12|2,13' UNION ALL
SELECT 7,2,'0,14|2,15|5,11' UNION ALL
SELECT 8,2,'0,17|3,13|2,18' UNION ALL
SELECT 9,2,'2,2|12,17|12,156' UNION ALL
SELECT 10,2,'0,7|6,19' UNION ALL
SELECT 11,2,'2,11|6,20' UNION ALL
SELECT 12,2,'2,16|20,28'/**/
go
select * from tb
order by (case when charindex('2,',sortid)>0 then convert(int,substring(sortid,charindex('2,',sortid)+2,charindex('|',sortid+'|',charindex('2,',sortid))-charindex('2,',sortid)-2)) else 0 end)desc,pkid desc
/*
pkID CateID SortID
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
10 2 0,7|6,19
4 2 NULL
3 3
1 5 0,2(12 行受影响)*/
go
drop table tb
--4楼改进,如果一列有多个 2,n 按 n 的最大值倒序排列:;with ach as
(
select a.pkID,a.CateID,substring(a.SortID,b.number,charindex('|',a.SortID+'|',b.number)-b.number) SortID
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.SortID)
and substring('|'+a.SortID,b.number,1) = '|'
)select a.*
from tb a left join ach b on a.pkID = b.pkID and b.SortID like '2,%'
where a.CateID = 2
group by a.pkID,a.CateID,a.SortID,b.pkID
order by (case when b.pkID is null then 1 else 0 end),
max(convert(int,right(b.SortID,len(b.SortID)-charindex(',',b.SortID)))) desc,a.pkID
from tb where CateID=2
order by
case when charindex('2,',SortID)>0
then cast(substring(SortID+',',charindex('2,',SortID)+2,
charindex(',',replace(SortID,'|',',')+',',
charindex('2,',SortID)+2)-charindex('2,',SortID)-2) as int)
else 0 end desc,pkID
/*
pkID CateID SortID
-----------------------------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
4 2 NULL
10 2 0,7|6,19(所影响的行数为 10 行)
*/
select *
from tb where CateID=2
order by
case when charindex('|2,','|'+SortID+'|')>0
then cast(substring(SortID+'|',charindex('|2,','|'+SortID+'|')+2,
charindex('|',SortID+'|',
charindex('|2,','|'+SortID+'|')+2)-charindex('|2,','|'+SortID)-2)
as int) else 0 end desc,pkID
我整理了一下,才知道,原来是这么回事:
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max))
INSERT INTO [tb]
SELECT 1,5,'0,2' UNION ALL
SELECT 2,6,'2,5' UNION ALL
SELECT 3,3,'' UNION ALL
SELECT 4,6,null UNION ALL
SELECT 5,6,'6,8' UNION ALL
SELECT 6,6,'0,12|6,13' UNION ALL
SELECT 7,6,'6,15|66,15|5,11' UNION ALL
SELECT 8,6,'0,17|6,16|22,18' UNION ALL
SELECT 9,6,'2,2|12,17|12,156' UNION ALL
SELECT 10,6,'0,7|6,19' UNION ALL
SELECT 11,6,'2,11|6,20' UNION ALL
SELECT 12,6,'2,123|2,12345' UNION ALL
SELECT 13,6,'2,16|20,28'
go
DECLARE @CateID INT; SET @CateID = 6;SELECT *,
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + 2,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + 2
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2
)
AS INT
)
ELSE
0
END
AS NewSortID
FROM tb WHERE CateID=@CateID
ORDER BY
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + 2,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + 2
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2
)
AS INT
)
ELSE
0
END
DESC
,pkID DESC
10 6 0,7|6,19 19
8 6 0,17|6,16|22,18 16
7 6 6,15|66,15|5,11 15
6 6 0,12|6,13 13
5 6 6,8 8
13 6 2,16|20,28 0
12 6 2,123|2,12345 0
9 6 2,2|12,17|12,156 0
4 6 NULL 0
2 6 2,5 0
这是搜索结果。
在将 nvarchar 值 ',1' 转换成数据类型 int 时失败。
我原库的数据如下:
9 15 NULL
16 20 20,1|2,1
30 20 20,3
32 20 20,4
35 15 NULL
16 20 20,1|2,6
30 20 5,8|20,3
32 20 6,12|20,4|9,9
35 15 NULL
看这个吧,有代表性一些,提示:
消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 ',1' 转换成数据类型 int 时失败。
DECLARE @CateID INT; SET @CateID = 20;
改为:
DECLARE @CateID INT; SET @CateID = 20;
时,就出现了错误提示,是怎么回事?消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 ',28' 转换成数据类型 int 时失败。IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max))
INSERT INTO [tb]
SELECT 1,5,'0,2'
UNION ALL SELECT 2,2,'2,1'
UNION ALL SELECT 3,3,''
UNION ALL SELECT 4,6,null
UNION ALL SELECT 5,2,'6,8'
UNION ALL SELECT 6,2,'0,12|6,13'
UNION ALL SELECT 7,5,'6,15|66,15|5,11'
UNION ALL SELECT 8,2,'0,17|6,16|22,18'
UNION ALL SELECT 9,2,'2,2|12,17|12,156'
UNION ALL SELECT 10,2,'0,7|6,19'
UNION ALL SELECT 11,2,'2,11|6,20'
UNION ALL SELECT 12,2,'2,123|2,12345'
UNION ALL SELECT 13,2,'2,16|20,28'
go
DECLARE @CateID INT; SET @CateID = 20;
SELECT *,
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + 2,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + 2
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2
)
AS INT
)
ELSE
0
END
AS NewSortID
FROM tb
DECLARE @CateID INT; SET @CateID = 2;
改为:
DECLARE @CateID INT; SET @CateID = 20;
时,就出现了错误提示,是怎么回事?
消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 ',28' 转换成数据类型 int 时失败。
消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 ',17' 转换成数据类型 int 时失败。
问题出在:当CateID与SortID不统一时,会提示这种错误,但是,这种情况,在我的系统设计里,是允许存在的,怎么才能排除这个?
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000))
INSERT INTO [tb]
SELECT 9,15,null UNION ALL
SELECT 16,20,'20,1|2,6' UNION ALL
SELECT 30,20,'5,8|20,3' UNION ALL
SELECT 32,20,'|6,12|20,4|9,9' UNION ALL
SELECT 35,15,null DECLARE @CateID INT; SET @CateID = 20;SELECT *,
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2 -len(RTRIM(@CateID))
)
AS INT
)
ELSE
0
END
AS NewSortID
FROM tb WHERE CateID=@CateID
ORDER BY
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2 -len(RTRIM(@CateID))
)
AS INT
)
ELSE
0
END
DESC
,pkID DESC
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max))
INSERT INTO [tb]
SELECT 1,5,'0,2'
UNION ALL SELECT 2,2,'2,1'
UNION ALL SELECT 3,3,''
UNION ALL SELECT 4,6,null
UNION ALL SELECT 5,2,'6,8'
UNION ALL SELECT 6,2,'0,12|6,13'
UNION ALL SELECT 7,5,'6,15|66,15|5,11'
UNION ALL SELECT 8,2,'0,17|6,16|22,18'
UNION ALL SELECT 9,2,'2,2|12,17|12,156'
UNION ALL SELECT 10,2,'0,7|6,19'
UNION ALL SELECT 11,2,'2,11|6,20'
UNION ALL SELECT 12,2,'2,123|2,12345'
UNION ALL SELECT 13,2,'2,16|20,28'DECLARE @CateID INT; SET @CateID = 20;SELECT *,
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2 -len(RTRIM(@CateID))
)
AS INT
)
ELSE
0
END
AS NewSortID
FROM tb
ORDER BY
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 2 -len(RTRIM(@CateID))
)
AS INT
)
ELSE
0
END
DESC
,pkID DESC
--结果是:
13 2 2,16|20,28 2
12 2 2,123|2,12345 0
11 2 2,11|6,20 0
10 2 0,7|6,19 0
9 2 2,2|12,17|12,156 0
8 2 0,17|6,16|22,18 0
7 5 6,15|66,15|5,11 0
6 2 0,12|6,13 0
5 2 6,8 0
4 6 NULL 0
3 3 0
2 2 2,1 0
1 5 0,2 0
--最后那行NewSortID也没有正确生成.谢谢豆子.
--
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max))
INSERT INTO [tb]
SELECT 1,5,'0,2'
UNION ALL SELECT 2,2,'2,1'
UNION ALL SELECT 3,3,''
UNION ALL SELECT 4,6,null
UNION ALL SELECT 5,2,'6,8'
UNION ALL SELECT 6,2,'0,12|6,13'
UNION ALL SELECT 7,5,'6,15|66,15|5,11'
UNION ALL SELECT 8,2,'0,17|6,16|22,18'
UNION ALL SELECT 9,2,'2,2|12,17|12,156'
UNION ALL SELECT 10,2,'0,7|6,19'
UNION ALL SELECT 11,2,'2,11|6,20'
UNION ALL SELECT 12,2,'2,123|2,12345'
UNION ALL SELECT 13,2,'2,16|20,28'DECLARE @CateID INT; SET @CateID = 20;SELECT *,
(case when isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)+2,2),'|','') end)
as newsortid
FROM [tb]
order by
(case when isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|' + RTRIM(@CateID) + ',','|'+[SortID]),0)+2,2),'|','') end) desc,pkid
王兄,你的代码一样存在这个问题,谢谢.
DECLARE @CateID INT; SET @CateID = 2 --可以改成20SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|'+ltrim(@CateID)+',','|'+
[SortID]),0)+len(@CateID)+1,len(@CateID)+1),'|','') end) desc,pkid
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000))
INSERT INTO [tb]
SELECT 1,5,'0,2'
UNION ALL SELECT 2,2,'2,1'
UNION ALL SELECT 3,3,''
UNION ALL SELECT 4,6,null
UNION ALL SELECT 5,2,'6,8'
UNION ALL SELECT 6,2,'0,12|6,13'
UNION ALL SELECT 7,5,'6,15|66,15|5,11'
UNION ALL SELECT 8,2,'0,17|6,16|22,18'
UNION ALL SELECT 9,2,'2,2|12,17|12,156'
UNION ALL SELECT 10,2,'0,7|6,19'
UNION ALL SELECT 11,2,'2,11|6,20'
UNION ALL SELECT 12,2,'2,123|2,12345'
UNION ALL SELECT 13,2,'2,16|20,28'DECLARE @CateID INT; SET @CateID = 20 --可以改成20SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|'+ltrim(@CateID)+',','|'+
[SortID]),0)+len(@CateID)+1,len(@CateID)+1),'|','') end) desc,pkid
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000))
insert into [tb]
select 1,5,'0,2' union all
select 2,6,'2,5' union all
select 3,3,'' union all
select 4,6,null union all
select 5,6,'6,8' union all
select 6,6,'0,12|6,13' union all
select 7,6,'6,15|66,15|5,11' union all
select 8,6,'0,17|6,16|22,18' union all
select 9,6,'2,2|12,17|12,156' union all
select 10,6,'0,7|6,19' union all
select 11,6,'2,11|6,20' union all
select 12,6,'2,123|2,12345' union all
select 13,6,'2,16|20,28'
godeclare @cateid int
set @cateid = 6select *
from tb
where cateid = @cateid
order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then
cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)),
charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))-
(charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid descdrop table tb/**************pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11 6 2,11|6,20
10 6 0,7|6,19
8 6 0,17|6,16|22,18
7 6 6,15|66,15|5,11
6 6 0,12|6,13
5 6 6,8
13 6 2,16|20,28
12 6 2,123|2,12345
9 6 2,2|12,17|12,156
4 6 NULL
2 6 2,5(11 行受影响)
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000))
insert into [tb]
select 1,5,'0,2'
union all select 2,2,'2,1'
union all select 3,3,''
union all select 4,6,null
union all select 5,2,'6,8'
union all select 6,2,'0,12|6,13'
union all select 7,5,'6,15|66,15|5,11'
union all select 8,2,'0,17|6,16|22,18'
union all select 9,2,'2,2|12,17|12,156'
union all select 10,2,'0,7|6,19'
union all select 11,2,'2,11|6,20'
union all select 12,2,'2,123|2,12345'
union all select 13,2,'2,16|20,28'
godeclare @cateid int
set @cateid = 2select *
from tb
where cateid = @cateid
order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then
cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)),
charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))-
(charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid desc/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 2 2,123|2,12345
13 2 2,16|20,28
11 2 2,11|6,20
9 2 2,2|12,17|12,156
2 2 2,1
10 2 0,7|6,19
8 2 0,17|6,16|22,18
6 2 0,12|6,13
5 2 6,8(9 行受影响)
*/select * from [tb] where cateid=@cateid
order by
(case when isnull(charindex('|'+ltrim(@cateid)+',','|'+[sortid]),0)=0 then 0
else replace(substring([sortid],isnull(charindex('|'+ltrim(@cateid)+',','|'+
[sortid]),0)+len(@cateid)+1,len(@cateid)+1),'|','') end) desc,pkid/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 2 2,16|20,28
12 2 2,123|2,12345
11 2 2,11|6,20
9 2 2,2|12,17|12,156
2 2 2,1
5 2 6,8
6 2 0,12|6,13
8 2 0,17|6,16|22,18
10 2 0,7|6,19(9 行受影响)
*/drop table tb
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000))
INSERT INTO [tb]
SELECT 1,5,'0,2'
UNION ALL SELECT 2,2,'2,1'
UNION ALL SELECT 3,3,''
UNION ALL SELECT 4,6,null
UNION ALL SELECT 5,2,'6,8'
UNION ALL SELECT 6,2,'0,12|6,13'
UNION ALL SELECT 7,5,'6,15|66,15|5,11'
UNION ALL SELECT 8,2,'0,17|6,16|22,18'
UNION ALL SELECT 9,2,'2,2|12,17|12,156'
UNION ALL SELECT 10,2,'0,7|6,19'
UNION ALL SELECT 11,2,'2,11|6,20'
UNION ALL SELECT 12,2,'2,123|2,12345'
UNION ALL SELECT 13,2,'2,16|20,28'
union all select 14,20,'20,19'
union all select 15,20,'2,12|20,18|9,1'
union all select 16,20,'2,1|8,9|20,20'
DECLARE @CateID INT; SET @CateID = 20
SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid
/*
pkID CateID SortID
----------- ----------- ------------------
15 20 2,12|20,18|9,1
16 20 2,1|8,9|20,20
14 20 20,19
*/
刚才没有数据是因为测试数据中没有CateID=20的数据。
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000))
insert into [tb]
select 1,5,'0,2'
union all select 2,2,'2,1'
union all select 3,3,''
union all select 4,6,null
union all select 5,2,'6,8'
union all select 6,2,'0,12|6,13'
union all select 7,5,'6,15|66,15|5,11'
union all select 8,2,'0,17|6,16|22,18'
union all select 9,2,'2,2|12,17|12,156'
union all select 10,2,'0,7|6,19'
union all select 11,2,'2,11|6,20'
union all select 12,2,'2,123|2,12345'
union all select 13,2,'2,16|20,28'
union all select 14,20,'20,19'
union all select 15,20,'2,12|20,18|9,1'
union all select 16,20,'2,1|8,9|20,20'
godeclare @cateid int
set @cateid = 2--me :
select *
from tb
where cateid = @cateid
order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then
cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)),
charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))-
(charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid desc/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 2 2,123|2,12345
13 2 2,16|20,28
11 2 2,11|6,20
9 2 2,2|12,17|12,156
2 2 2,1
10 2 0,7|6,19
8 2 0,17|6,16|22,18
6 2 0,12|6,13
5 2 6,8(9 行受影响)
*/--豆子:
select * from [tb] where cateid=@cateid
order by
(case when isnull(charindex('|'+ltrim(@cateid)+',','|'+[sortid]),0)=0 then 0
else replace(substring([sortid],isnull(charindex('|'+ltrim(@cateid)+',','|'+
[sortid]),0)+len(@cateid)+1,len(@cateid)+1),'|','') end) desc,pkid/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 2 2,16|20,28
12 2 2,123|2,12345
11 2 2,11|6,20
9 2 2,2|12,17|12,156
2 2 2,1
5 2 6,8
6 2 0,12|6,13
8 2 0,17|6,16|22,18
10 2 0,7|6,19(9 行受影响)
*/--老王大哥:
select * from [tb] where cateid=@cateid
order by
(case when isnull(charindex('|2,','|'+[sortid]),0)=0 then 0
else replace(substring([sortid],isnull(charindex('|2,','|'+[sortid]),0)+2,2),'|','') end) desc,pkid/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 2 2,16|20,28
12 2 2,123|2,12345
11 2 2,11|6,20
9 2 2,2|12,17|12,156
2 2 2,1
5 2 6,8
6 2 0,12|6,13
8 2 0,17|6,16|22,18
10 2 0,7|6,19(9 行受影响)
*/drop table tb
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
GO
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](4000))
INSERT INTO [tb]
SELECT 1,5,'0,2'
UNION ALL SELECT 2,2,'2,1'
UNION ALL SELECT 3,3,''
UNION ALL SELECT 4,6,null
UNION ALL SELECT 5,2,'6,8'
UNION ALL SELECT 6,2,'0,12|6,13'
UNION ALL SELECT 7,5,'6,15|66,15|5,11'
UNION ALL SELECT 8,2,'0,17|6,16|22,18'
UNION ALL SELECT 9,2,'2,2|12,17|12,156'
UNION ALL SELECT 10,2,'0,7|6,19'
UNION ALL SELECT 11,2,'2,11|6,20'
UNION ALL SELECT 12,2,'2,123|2,12345'
UNION ALL SELECT 13,2,'2,16|20,28'
union all select 14,20,'20,19'
union all select 15,20,'2,12|20,18|9,1'
union all select 16,20,'2,1|8,9|20,20'DECLARE @CateID INT; SET @CateID = 20
SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|'+ltrim(@CateID)+',','|'+
[SortID]),0)+len(@CateID)+1,len(@CateID)+1),'|','') end) desc,pkid/*
pkID CateID SortID
----------- ----------- -----------------
16 20 2,1|8,9|20,20
14 20 20,19
15 20 2,12|20,18|9,1
*/刚才发的是以前的,33楼的应该没有问题。
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](4000))
insert into [tb]
select 1,5,'0,2'
union all select 2,2,'200,1'
union all select 3,3,''
union all select 4,6,null
union all select 5,200,'6,8'
union all select 6,200,'0,12|6,13'
union all select 7,5,'6,15|66,15|5,11'
union all select 8,200,'0,17|6,16|22,18'
union all select 9,200,'200,2|12,17|12,156'
union all select 10,200,'0,7|6,19'
union all select 11,200,'2,11|6,20'
union all select 12,200,'2,123|200,12345'
union all select 13,200,'2,16|200,28'
union all select 14,200,'20,19'
union all select 15,200,'2,12|200,18|9,1'
union all select 16,200,'2,1|8,9|200,20'
godeclare @cateid int
set @cateid = 200--me :
select *
from tb
where cateid = @cateid
order by (case when charindex('|'+ltrim(@cateid)+',','|'+sortid) > 0 then
cast(substring(sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)),
charindex('|',sortid+'|',charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))-
(charindex('|'+ltrim(@cateid)+',','|'+sortid)+1+len(ltrim(@cateid)))) as int) else 0 end) desc,pkid desc/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 200 2,123|200,12345
13 200 2,16|200,28
16 200 2,1|8,9|200,20
15 200 2,12|200,18|9,1
9 200 200,2|12,17|12,156
14 200 20,19
11 200 2,11|6,20
10 200 0,7|6,19
8 200 0,17|6,16|22,18
6 200 0,12|6,13
5 200 6,8(11 行受影响)
*/--豆子:
select * from [tb] where cateid=@cateid
order by
(case when isnull(charindex('|'+ltrim(@cateid)+',','|'+[sortid]),0)=0 then 0
else replace(substring([sortid],isnull(charindex('|'+ltrim(@cateid)+',','|'+
[sortid]),0)+len(@cateid)+1,len(@cateid)+1),'|','') end) desc,pkid/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 200 2,123|200,12345 --200,12345
9 200 200,2|12,17|12,156 --200,2
15 200 2,12|200,18|9,1 --200,18
13 200 2,16|200,28 --200,28
16 200 2,1|8,9|200,20 --20020
5 200 6,8
6 200 0,12|6,13
8 200 0,17|6,16|22,18
10 200 0,7|6,19
11 200 2,11|6,20
14 200 20,19(11 行受影响)
*/--老王大哥:
select * from [tb] where cateid=@cateid
order by
(case when isnull(charindex('|2,','|'+[sortid]),0)=0 then 0
else replace(substring([sortid],isnull(charindex('|2,','|'+[sortid]),0)+2,2),'|','') end) desc,pkid/*
pkid cateid sortid
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 200 2,16|200,28 --200,28
12 200 2,123|200,12345
15 200 2,12|200,18|9,1 --200,18
11 200 2,11|6,20 --没有200
16 200 2,1|8,9|200,20 --200,20
5 200 6,8
6 200 0,12|6,13
8 200 0,17|6,16|22,18
9 200 200,2|12,17|12,156
10 200 0,7|6,19
14 200 20,19(11 行受影响)
*/drop table tb
。。
insert into [tb]
select 1,5,'0,2'
union all select 2,2,'200,1'
union all select 3,3,''
union all select 4,6,null
union all select 5,200,'6,8'
union all select 6,200,'0,12|6,13'
union all select 7,5,'6,15|66,15|5,11'
union all select 8,200,'0,17|6,16|22,18'
union all select 9,200,'200,2|12,17|12,156'
union all select 10,200,'0,7|6,19'
union all select 11,200,'2,11|6,20'
union all select 12,200,'2,123|200,12345'
union all select 13,200,'2,16|200,28'
union all select 14,200,'20,19'
union all select 15,200,'2,12|200,18|9,1'
union all select 16,200,'2,1|8,9|200,20'
goDECLARE @CateID INT; SET @CateID = 200;SELECT *,
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 1 -len(RTRIM(@CateID))
)
AS INT
)
ELSE
0
END as NewSortID
FROM tb WHERE CateID=@CateID ORDER BY
CASE WHEN CHARINDEX('|' + RTRIM(@CateID) + ',', '|' + SortID + '|') > 0
THEN CAST
(
SUBSTRING
(
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1,
CHARINDEX
(
'|',
SortID + '|',
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID + '|'
) + len(RTRIM(@CateID))+1
) -
CHARINDEX
(
'|' + RTRIM(@CateID) + ',',
'|' + SortID
) - 1 -len(RTRIM(@CateID))
)
AS INT
)
ELSE
0
END
DESC
,pkID DESC
/*
pkid cateid sortid NewSortID
----------- ----------- -------------------------------
12 200 2,123|200,12345 12345
13 200 2,16|200,28 28
16 200 2,1|8,9|200,20 20
15 200 2,12|200,18|9,1 18
9 200 200,2|12,17|12,156 2
14 200 20,19 0
11 200 2,11|6,20 0
10 200 0,7|6,19 0
8 200 0,17|6,16|22,18 0
5 200 6,8 0(所影响的行数为 11 行)*/
IF (OBJECT_ID('[tb]') IS NOT NULL) DROP TABLE [tb]
go
create table [tb] ([pkid] [int],[cateid] [int],[sortid] [nvarchar](20))
insert into [tb]
select 1,5,'0,2'
union all select 2,2,'200,1'
union all select 3,3,''
union all select 4,6,null
union all select 5,200,'6,8'
union all select 6,200,'0,12|6,13'
union all select 7,5,'6,15|66,15|5,11'
union all select 8,200,'0,17|6,16|22,18'
union all select 9,200,'200,2|12,17|12,156'
union all select 10,200,'0,7|6,19'
union all select 11,200,'2,11|6,20'
union all select 12,200,'2,123|200,12345'
union all select 13,200,'2,16|200,28'
union all select 14,200,'20,19'
union all select 15,200,'2,12|200,18|9,1'
union all select 16,200,'2,1|8,9|200,20'
gogo
declare @cateid int;set @cateid = 200select * from tb where cateid=@cateid
order by case when isnull(charindex('|'+ltrim(@CateID)+',','|'+[SortID]),0)=0 then 0 else
left(right([SortID]+'|',len([SortID]+'|')-
charindex('|'+ltrim(@CateID)+',','|'+[SortID])-len(@CateID)),
charindex('|',right([SortID]+'|',len([SortID]+'|')-
charindex('|'+ltrim(@CateID)+',','|'+[SortID])-len(@CateID)) )-1) end desc,pkid
/*
pkid cateid sortid
----------- ----------- --------------------
12 200 2,123|200,12345
13 200 2,16|200,28
16 200 2,1|8,9|200,20
15 200 2,12|200,18|9,1
9 200 200,2|12,17|12,156
5 200 6,8
6 200 0,12|6,13
8 200 0,17|6,16|22,18
10 200 0,7|6,19
11 200 2,11|6,20
14 200 20,19
*/
DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid
/*
pkID CateID SortID
----------- ----------- -------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
4 2 NULL
10 2 0,7|6,19
*/
DECLARE @CateID INT; SET @CateID = 2
SELECT * FROM [tb] WHERE CateID=@CateID
order by
(case when isnull(charindex('|2,','|'+[SortID]),0)=0 then 0
else replace(substring([SortID],isnull(charindex('|2,','|'+[SortID]),0)+2,2),'|','') end) desc,pkid
/*
pkID CateID SortID
----------- ----------- -------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
4 2 NULL
10 2 0,7|6,19
*/
如果存在类似于 22,5 之类的,如((如果有其他条件,则加进去就是了):
CREATE TABLE [tb] ([pkID] [int],[CateID] [int],[SortID] [nvarchar](max))
INSERT INTO [tb]
SELECT 1,5,'0,2' UNION ALL
SELECT 2,2,'2,5' UNION ALL
SELECT 3,3,'' UNION ALL
SELECT 4,2,null UNION ALL
SELECT 5,2,'2,8' UNION ALL
SELECT 6,2,'0,12|2,13' UNION ALL
SELECT 7,2,'0,14|2,15|5,11' UNION ALL
SELECT 8,2,'0,17|3,13|2,18' UNION ALL
SELECT 9,2,'2,2|12,17|12,156' UNION ALL
SELECT 10,2,'0,7|6,19' UNION ALL
SELECT 11,2,'2,11|6,20' UNION ALL
SELECT 12,2,'2,16|20,28' UNION ALL
select 13,2,'22,20' UNION ALL --这两行是添加的含 22,xx 的
select 14,2,'0,25|22,2'
go
select * from tb
order by (case when charindex('|2,','|'+sortid)>0 then
substring(sortid, charindex('|2,','|'+sortid)+2,
charindex('|',sortid+'|',charindex('|2,','|'+sortid)+2)-charindex('|2,','|'+sortid)-2)
else 0 end) desc,pkid desc
/*
pkID CateID SortID
----------- ----------- -----------------------------------------------------
8 2 0,17|3,13|2,18
12 2 2,16|20,28
7 2 0,14|2,15|5,11
6 2 0,12|2,13
11 2 2,11|6,20
5 2 2,8
2 2 2,5
9 2 2,2|12,17|12,156
14 2 0,25|22,2
13 2 22,20
10 2 0,7|6,19
4 2 NULL
3 3
1 5 0,2(14 行受影响)
*/
go
drop table tb