select row_number() over(order by SID Desc)as RowIndex,n.* from Sample n ";现在有这条查询语句,我现在要实现一个原来基础上的排序:有一个字段:Unlook(字符串类型),存放很多名字(顿号分隔):张三、李四例如当前用户是:张三(后台代码user是当前用户名字的变量),那么所有的记录中Unlook字段中带有张三的就排在前面,没有的就排后面。然后再根据SID来作倒序。
请各位好心人帮帮忙,万分感谢!
请各位好心人帮帮忙,万分感谢!
CREATE TABLE #tmp2
(
SID INT,
UnLook NVARCHAR(200)
)INSERT INTO #tmp2 VALUEs(1,'张三,李四')
INSERT INTO #tmp2 VALUEs(2,'王五,赵六')
INSERT INTO #tmp2 VALUEs(3,'王五,张三')
INSERT INTO #tmp2 VALUEs(3,'王六,王七')
INSERT INTO #tmp2 VALUEs(3,'王八,王九')
SELECT *,CHARINDEX('张三',unlook) FROM #tmp2 ORDER BY (CASE WHEN CHARINDEX('张三',unlook)>0 THEN 'a' else 'b' end),sid asc结果:
1 张三,李四 1
3 王五,张三 4
2 王五,赵六 0
3 王六,王七 0
3 王八,王九 0
您太帅了,非常感谢!!then 'a' else 'b' 在这里有什么意义的吗?
高手,我现在遇到问题,请教一下。select row_number() over(order by SID)as RowIndex,n.*,CHARINDEX('张三',n.Unlook) from MM_SampleList n
where n.IsPublic=1 and n.S_Cate = '公文通告' and n.S_AuditingType = 2 or n.SID
in(select distinct n.SID from MM_SampleList n join NB_NoticeToUser b on b.NoticeID=n.SID
and((b.UserType='U' and b.UserID='182') or (b.UserType='R' and b.UserID
in(select R_RoleID from sys_UserRoles where R_UserID='182')) or (b.UserType='G' and b.UserID='92')
or (b.UserType='U' and b.UserID='182')) where n.IsPublic=0 and n.S_Cate = '公文通告'
and n.S_AuditingType = 2)
Order By(CASE WHEN CHARINDEX('张三',n.Unlook)>0 THEN 'a' else 'b' end),n.SID Desc上面这条是没有问题的,但是我现在在外层加了一个查询条件就报错了,如下:
select * from
(
select row_number() over(order by SID)as RowIndex,n.*,CHARINDEX('张三',n.Unlook)
from MM_SampleList n
where n.IsPublic=1 and n.S_Cate = '公文通告' and n.S_AuditingType = 2 or n.SID in
(select distinct n.SID from MM_SampleList n join NB_NoticeToUser b on b.NoticeID=n.SID and
((b.UserType='U' and b.UserID='182') or
(b.UserType='R' and b.UserID in(select R_RoleID from sys_UserRoles where R_UserID='182'))
or (b.UserType='G' and b.UserID='92') or (b.UserType='U' and b.UserID='182'))
where n.IsPublic=0 and n.S_Cate = '公文通告'
and n.S_AuditingType = 2)
Order By (CASE WHEN CHARINDEX('张三',n.Unlook)>0 THEN 'a' else 'b' end),n.SID Desc
) as temp
Where RowIndex between 1 and 15错误提示如下:
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。请问这是什么原因呢,怎么解决?