没办法,做了一点改进 再用排名函数 create table t_order ( id varchar(10), name1 varchar(20), name2 varchar(20), displayorder varchar(10) ) 1,aaaaaa,______,3 2,bbbbbb,______,1 3,aaaaaa,222222,5 4,bbbbbb,222222,2 5,cccccc,222222,4 6,bbbbbb,333333,0 7,cccccc,______,6insert into t_order values ('1','aaaaaa','______',3) insert into t_order values ('2','bbbbbb','______',1) insert into t_order values ('3','aaaaaa','222222',5) insert into t_order values ('4','bbbbbb','222222',2) insert into t_order values ('5','cccccc','222222',4) insert into t_order values ('6','bbbbbb','333333',0) insert into t_order values ('7','cccccc','______',6) update t_order set name2 = '-1' where name2='______' select RANK() over (partition by name1 order by displayorder,name1,cast(name2 as int)) as order2, * from t_order order by displayorder
下面贴出我的解决代码,和大家分享吧。CREATE TABLE [TestTable] ( [ID] [smallint] IDENTITY(1,1) NOT NULL, [Name1] [nvarchar](30) NOT NULL, [Name2] [nvarchar](30) NOT NULL, [DisplayOrder] [smallint] NOT NULL ) ON [PRIMARY] SELECT T.* FROM TestTable T LEFT JOIN (SELECT Name1, IndexID FROM ( SELECT Name1, Name2, ROW_NUMBER() OVER(ORDER BY DisplayOrder) AS IndexID FROM TestTable ) AS IndexTable WHERE Name2='' ) F ON T.Name1 = F.Name1 ORDER BY F.IndexID, CASE WHEN T.Name2='' THEN 0 ELSE 1 END, T.DisplayOrder
我是先按照displayorder对name1排序,然后在name1相同的情况下,再按照displayorder对name2排序,其中的特例就是name2为‘_’的需要排在该组的第一个,谢谢你。
再用排名函数
create table t_order
(
id varchar(10),
name1 varchar(20),
name2 varchar(20),
displayorder varchar(10)
)
1,aaaaaa,______,3
2,bbbbbb,______,1
3,aaaaaa,222222,5
4,bbbbbb,222222,2
5,cccccc,222222,4
6,bbbbbb,333333,0
7,cccccc,______,6insert into t_order values ('1','aaaaaa','______',3)
insert into t_order values ('2','bbbbbb','______',1)
insert into t_order values ('3','aaaaaa','222222',5)
insert into t_order values ('4','bbbbbb','222222',2)
insert into t_order values ('5','cccccc','222222',4)
insert into t_order values ('6','bbbbbb','333333',0)
insert into t_order values ('7','cccccc','______',6)
update t_order set name2 = '-1' where name2='______'
select RANK() over (partition by name1 order by displayorder,name1,cast(name2 as int)) as order2, * from t_order order by displayorder
-------------------- ---------- -------------------- -------------------- ------------
1 6 bbbbbb 333333 0
2 2 bbbbbb -1 1
3 4 bbbbbb 222222 2
1 1 aaaaaa -1 3
1 5 cccccc 222222 4
2 3 aaaaaa 222222 5
2 7 cccccc -1 6(7 行受影响)
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name1] [nvarchar](30) NOT NULL,
[Name2] [nvarchar](30) NOT NULL,
[DisplayOrder] [smallint] NOT NULL
) ON [PRIMARY]
SELECT
T.*
FROM
TestTable T
LEFT JOIN
(SELECT
Name1, IndexID
FROM
(
SELECT
Name1, Name2, ROW_NUMBER() OVER(ORDER BY DisplayOrder) AS IndexID
FROM
TestTable
) AS IndexTable
WHERE
Name2=''
) F
ON
T.Name1 = F.Name1
ORDER BY
F.IndexID, CASE WHEN T.Name2='' THEN 0 ELSE 1 END, T.DisplayOrder