查一个表中某个字段相同的其他所有用户信息



SELECT g.WriteDate,a.Accounts,('' ) as 'CardType',g.basecell,g.winscore
                                       ,STUFF((select a1.Accounts+':'+( '') +', '
                                            FROM dbo.gametable g1
                                            JOIN usertable a1
                                            ON g1.UserID=a1.UserID
                                         WHERE g.GameNum =g1.GameNum
                                         AND g.UserID<>g1.UserID
                                         FOR XML PATH('')),1,1,'') as 'other'
                                       ,row_number() over(order by WriteDate desc,Accounts asc) rowNumber
                                    FROM dbo.gametable g
                                    JOIN usertable a
                                    ON g.UserID=a.UserID
                                    WHERE 1=1