declare @t table(name varchar(),qy varchar(),je int)

insert into @t

select '产品一','北京',

union all select '产品一','上海',

union all select '产品二','北京',

union all select '产品三','上海',

union all select '产品三','北京',

union all select '产品四','上海',

--select * from @t a where not exists  --这是取表中的NAME相同的最大值

--(

--    select  from @t where name=a.name and je>a.je

--)

--第一个答案：

SELECT NAME,QY,JE-ISNULL((SELECT JE FROM @T WHERE NAME=A.NAME AND QY<>A.QY),)

FROM @T A

WHERE JE=(SELECT MAX(JE) FROM @T WHERE NAME=A.NAME)

ORDER BY NAME

--第二个答案

Select

      Name,

      qy,

      je-(Select

                Case When Min(je)=A.je Then  Else Min(je) End

          From

                @t

          Where Name=A.Name Group By Name) As je

From

      @t A

Where Not Exists

     (Select  From @t Where Name=A.Name And je>A.je)