如果没有理解错你的意思的话,我想是这样:
select distinct tabcontent.id,content,authorid,fixerid,UserName from tabcontent,tabuser where tabcontent.Authorid = tabuser.id or fixerid = tabuser.id
select distinct tabcontent.id,content,authorid,fixerid,UserName from tabcontent,tabuser where tabcontent.Authorid = tabuser.id or fixerid = tabuser.id
b.UserName as AuthorName,
c.UserName as FixerName
from TABContent as a
left outer join TABUser as b on a.AuthorID=b.ID
left outer join TABUser as c on a.FixerID=c.ID
where u.id in (c.AuthorID,c.FixerID)数据多的话千万别用这样的语句,否则慢得向蜗牛!!!!
一、先联接后联合
SELECT DISTINCT U.UserName
FROM TABContent C
INNER JOIN TABUser U
ON U.ID = C.FixerID
UNION
SELECT DISTINCT U.UserName
FROM TABContent C
INNER JOIN TABUser U
ON U.ID = C.AuthorID
二、先联合后联接
SELECT DISTINCT U.UserName
FROM TABUser U
INNER JOIN (SELECT DISTINCT AuthorID UID
FROM TABContent
UNION
SELECT DISTINCT FixerID UID
FROM TABContent) C
ON C.UID = U.UserName
应当说第二个方案好一些,可我怎么都觉得它挺邪的,恐怕也只有SQL Server才认这种东西。
AuthorID AuthorName FixerID FixerName
那也有两种方法
SELECT C.AuthorID, U1.Name AutorName, C.FixerID, U2.Name FixerName
FROM TABContent C
LEFT OUTER JOIN TABUser U1
ON C.AuthorID = U1.ID
LEFT OUTER JOIN TABUser U2
ON C.AuthorID = U2.IDSELECT C.AuthorID,
(SELECT U1.UserName FROM TABUser U1 WHERE U1.ID = C.AuthorID) AutorName,
C.FixerID,
(SELECT U2.UserName FROM TABUser U2 WHERE U2.ID = C.FixerID) FixerName
FROM TABContent C
我看这两种应当是第一种好。