SELECT * FROM(
select ROW_NUMBER() over(partition BY sid order by cscore desc) as tid,sid,cname,cscore FROM dbo.aright
) A WHERE tid=1

注：row_number() 和 partition by order by 来实现 组内排序

 例：

SELECT * FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY R.sid ORDER BY cscore desc) rowid,L.sid,L.sname,R.cname,R.cscore FROM dbo.aleft L, dbo.aright R WHERE L.sid=R.sid
) A WHERE A.rowid=1

 结果 

 另外可以用CROSS APPLY实现：

SELECT L.*,C.cname,c.cscore FROM dbo.aleft L CROSS APPLY (
SELECT TOP 1 * FROM dbo.aright R WHERE r.sid=l.sid ORDER BY R.cscore DESC
) AS C

结果相同。

cross Apply 语法可以看：

http://www.cnblogs.com/Leo_wl/archive/2013/04/02/2997012.html

 借例子看下以下分组汇总：

SELECT L.sname,R.cname,SUM(R.cscore) FROM dbo.aleft L, dbo.aright R WHERE L.sid=R.sid GROUP BY L.sname,R.cname WITH CUBE

CUBE 生成的结果集显示了所选列中值的所有组合的聚合

本例中，所有组合被解释为：sname、cname的分组。