--设user表中姓名列名为 userNameSELECT ISNULL(b.userName,'guest') 作者,ISNULL(c.userName,'guest') 回复者 FROM fatie a LEFT JOIN [user] b ON a.authorId = b.userId LEFT JOIN [user] c ON a.lastEditorId = c.userId
select case when authorid=0 and lasteditorid=0 then '游客' else authorid end from fatie
(case when authorId='' then '游客' else authorId end) as authorId, (case when lastEditorId='' then '游客' else lastEditorId end) as lastEditorId,不过建议你这个最好还是前台去判断才好。
select case when authorId = 0 and lastEditorId = 0 then '游客' else u1.user_name end 作者, case when authorId = 0 and lastEditorId = 0 then '游客' else u2.user_name end 回复者 from fatie left outer join [user] u1 on u1.userid = fatie.authorId left outer join [user] u2 on u2.userid = fatie.lastEditorId
再加ISNULL判断select case when isnull(authorId,0) = 0 and isnull(lastEditorId,0) = 0 then '游客' else u1.user_name end 作者, case when isnull(authorId,0) = 0 and isnull(lastEditorId,0) = 0 then '游客' else u2.user_name end 回复者 from fatie left outer join [user] u1 on u1.userid = fatie.authorId left outer join [user] u2 on u2.userid = fatie.lastEditorId
FROM fatie a
LEFT JOIN [user] b
ON a.authorId = b.userId
LEFT JOIN [user] c
ON a.lastEditorId = c.userId
(case when lastEditorId='' then '游客' else lastEditorId end) as lastEditorId,不过建议你这个最好还是前台去判断才好。
select
case when authorId = 0 and lastEditorId = 0 then
'游客'
else
u1.user_name
end 作者,
case when authorId = 0 and lastEditorId = 0 then
'游客'
else
u2.user_name
end 回复者
from fatie
left outer join [user] u1 on u1.userid = fatie.authorId
left outer join [user] u2 on u2.userid = fatie.lastEditorId
不对啊,authorId 和 lastEditorId 都是bigInt型的,和userid类型一样啊isnull好象不能判断bigInt型的吧
case when isnull(authorId,0) = 0 and isnull(lastEditorId,0) = 0 then
'游客'
else
u1.user_name
end 作者,
case when isnull(authorId,0) = 0 and isnull(lastEditorId,0) = 0 then
'游客'
else
u2.user_name
end 回复者
from fatie
left outer join [user] u1 on u1.userid = fatie.authorId
left outer join [user] u2 on u2.userid = fatie.lastEditorId
试过才知道。而且我处理是用户名并不是userid.另外,isnull你看下联机丛书就知道它能不能用了。
insert @fatie select 1,3
insert @fatie select 4,2
declare @user table(userid bigint,username nvarchar(100))
insert @user select 1,'張三'
insert @user select 2,'李四'
SELECT ISNULL(b.userName,'遊客') 作者,ISNULL(c.userName,'遊客') 回复者
FROM @fatie a
LEFT JOIN @user b
ON a.authorId = b.userId
LEFT JOIN @user c
ON a.lastEditorId = c.userId
/*
作者 回复者
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
張三 遊客
遊客 李四*/