组内按一定规则排序取第一名

[chengangcsdn]

declare @table table(projectid int,FillTotal int,FillQty int,SendTotal int)
insert into @table
select 1,100,5,2 union all
select 1,200,0,5 union all
select 2,0,10,3 union all
select 2,0,20,6
-------------
要的结果是 projectid分组，取order by FillTotal desc,FillQty desc 第一名
结果集该是
1,200,0,5
2,0,20,6

[吉普赛的歌]

SELECT 
	projectid 
	,FillTotal 
	,FillQty 
	,SendTotal 
FROM (
	SELECT ROW_NUMBER() OVER (PARTITION BY projectid ORDER BY FillTotal desc,FillQty desc) AS rid
		,*
	FROM @table 
) AS t 
WHERE t.rid=1
/*
projectid   FillTotal   FillQty     SendTotal
----------- ----------- ----------- -----------
1           200         0           5
2           0           20          6 
*/

[二月十六]

楼主其实在说规则的时候已经也就写完了把语句，只是对这这个知识点欠缺

declare @table table(projectid int,FillTotal int,FillQty int,SendTotal int)
insert into @table
select 1,100,5,2 union all
select 1,200,0,5 union all
select 2,0,10,3 union all
select 2,0,20,6 
SELECT
    *
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY				--projectid分组
                                   projectid
                               ORDER BY					--order by FillTotal desc,FillQty desc
                                   FillTotal DESC,
                                   FillQty DESC
                              ) rn
        FROM
            @table
    ) t
WHERE
    rn = 1;

[chengangcsdn]

忘记了补充，要兼容SQL2000 不能用OVER

[二月十六]

SELECT
    projectid,
    (
        SELECT TOP 1
            FillTotal
        FROM
            @table
        WHERE
            a.projectid = projectid
        ORDER BY
            FillTotal DESC,
            FillQty DESC
    ) FillTotal,
    (
        SELECT TOP 1
            FillQty
        FROM
            @table
        WHERE
            a.projectid = projectid
        ORDER BY
            FillTotal DESC,
            FillQty DESC
    ) FillQty,
    (
        SELECT TOP 1
            SendTotal
        FROM
            @table
        WHERE
            a.projectid = projectid
        ORDER BY
            FillTotal DESC,
            FillQty DESC
    ) SendTotal
FROM
    @table a
GROUP BY
    projectid

[RINK_1]

select * 
from
(select * 
from
@table A
where not exists (select 1 from @table where A.projectid=projectid and filltotal>A.filltotal)) as A
where not exists (select 1 from @table where A.projectid=projectid and filltotal=A.filltotal and FillQty>A.FillQty)