select t1.* from 
(
select m.CLIENTID , n.GOODSID , sum(n.QTY) qty
from S_ORDER m , S_ORDERD n
where m.BILLID = n.BILLID
group by m.CLIENTID , n.GOODSID
) t1 where t1.qty = (select max(qty) from 
(
select m.CLIENTID , n.GOODSID , sum(n.QTY) qty
from S_ORDER m , S_ORDERD n
where m.BILLID = n.BILLID
group by m.CLIENTID , n.GOODSID
) t2 where t1.GOODSID = t2.GOODSID)

------解决方案--------------------
SQL code
----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-30 15:39:05
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[S_ORDER]
if object_id('[S_ORDER]') is not null drop table [S_ORDER]
go 
create table [S_ORDER]([BILLID] varchar(6),[CLIENTID] varchar(8))
insert [S_ORDER]
select '1001','A01' union all
select '1002','K01' union all
select '1003','B03' union all
select '1004','H08' union all
select '1005','A01'
--> 测试数据：[S_ORDERD]
if object_id('[S_ORDERD]') is not null drop table [S_ORDERD]
go 
create table [S_ORDERD]([BILLID] int,[GOODSID] int,[QTY] int)
insert [S_ORDERD]
select 1001,9001,30 union all
select 1001,9003,70 union all
select 1002,9001,80 union all
select 1003,9001,300 union all
select 1004,9988,700 union all
select 1005,9001,600
--------------开始查询--------------------------
select 
  *
into 
#tb
from
(select 
   a.CLIENTID,b.GOODSID,b.qty
from
   S_ORDER a,
   (select BILLID,GOODSID,(select SUM(qty) from [S_ORDERD] where GOODSID=t.GOODSID) as qty from [S_ORDERD] t)b
where
   a.BILLID=b.BILLID)t
   
 select distinct * from #tb t where qty=(select MAX(qty) from #tb where CLIENTID=t.CLIENTID)
 
 
 drop table #tb 
----------------结果----------------------------
/* CLIENTID GOODSID     qty
-------- ----------- -----------
A01      9001        1010
B03      9001        1010
H08      9988        700
K01      9001        1010

(4 行受影响)
*/

------解决方案--------------------
SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T1') is null
    drop table #T1
Go
Create table #T1([BILLID] int,[CLIENTID] nvarchar(3))
Insert #T1
select 1001,N'A01' union all
select 1002,N'K01' union all
select 1003,N'B03' union all
select 1004,N'H08' union all
select 1005,N'A01'
Go

 
if not object_id(N'Tempdb..#T2') is null
    drop table #T2
Go
Create table #T2([BILLID] int,[GOODSID] int,[QTY] int)
Insert #T2
select 1001,'9001',30 union all
select 1001,'9003',70 union all
select 1002,'9001',80 union all
select 1003,'9001',300 union all
select 1004,'9988',700 union all
select 1005,'9001',600
Go

SELECT a.[CLIENTID],a.[GOODSID],b.[QTY]
FROM  (Select b.[BILLID],b.[CLIENTID],a.[GOODSID] ,SUM(a.[QTY]) AS [QTY] FROM #T2 AS a  INNER JOIN #T1 AS b  ON a.BILLID=b.[BILLID] GROUP BY b.[CLIENTID],a.[GOODSID],b.[BILLID]) AS a 
    INNER JOIN (SELECT [GOODSID],SUM([QTY]) AS [QTY] FROM #T2  GROUP BY [GOODSID]) AS b ON a.