求一SQL查询语句

[cch1010]

billcode	payway	 paymoney
09030006	预留款	 50000.00
09030006	现金	 50000.00
09030006	信用卡	 120000.00

09030003	现金	 305708.92
09030003	信用卡	 1000000.00

（查询一个单号中（billcode）付款金额（paymoney）为最大值的一项，将这项的付款方式（payway）设为主付款方式，并算出各单号的付款总金额）

如何得到如下结果：

billcode       mainPayway     sumPaymoney
09030006        信用卡            220000.00
09030003        信用卡            1305708.92

[mugua604]

create table tb 
(billcode varchar(20)   
,payway  nvarchar(10) 
,paymoney numeric(10,2))

insert into tb select 
'09030006',    N'预留款',     50000.00
insert into tb select 
'09030006',    N'现金',     50000.00
insert into tb select 
'09030006',    N'信用卡',     120000.00
insert into tb select 
'09030003',    N'现金',     305708.92
insert into tb select 
'09030003',    N'信用卡',     1000000.00



select a.billcode,sum(a.paymoney) paymoney,b.payway
from tb a
left join (select * from tb a where not exists(select 1 from tb where a.billcode=billcode and a.paymoney<paymoney)) b
    on a.billcode = b.billcode
group by a.billcode,b.payway

/*
billcode             paymoney                                payway
-------------------- --------------------------------------- ----------
09030003             1305708.92                              信用卡
09030006             220000.00                               信用卡

(2 行受影响)

*/

[ws_hgo]

insert into #tb select 
'09030006',    N'预留款',     50000.00
insert into #tb select 
'09030006',    N'现金',     50000.00
insert into #tb select 
'09030006',    N'信用卡',     120000.00
insert into #tb select 
'09030003',    N'现金',     305708.92
insert into #tb select 
'09030003',    N'信用卡',     1000000.00

select b.billcode,b.payway,t.paymoney from 
(
 select billcode,payway from #tb t where not exists(select * from #tb where billcode=t.billcode and paymoney>t.paymoney)
) b join
(
 select billcode,sum(paymoney) paymoney from #tb group by billcode
) t on t.billcode=b.billcode

[ws_hgo]

insert into #tb select 
'09030006',    N'预留款',     50000.00
insert into #tb select 
'09030006',    N'现金',     50000.00
insert into #tb select 
'09030006',    N'信用卡',     120000.00
insert into #tb select 
'09030003',    N'现金',     305708.92
insert into #tb select 
'09030003',    N'信用卡',     1000000.00

select b.billcode,b.payway,t.paymoney from 
(
 select billcode,payway from #tb t where not exists(select * from #tb where billcode=t.billcode and paymoney>t.paymoney)
) b join
(
 select billcode,sum(paymoney) paymoney from #tb group by billcode
) t on t.billcode=b.billcode


billcode             payway     paymoney
-------------------- ---------- ---------------------------------------
09030003             信用卡        1305708.92
09030006             信用卡        220000.00

(2 行受影响)

[ws_hgo]

人齐结贴

[rucypli]

select billcode,'信用卡' as mainpayway,sum(paymoney) as sumpaymoney
from tb
group by billcode

[百年树人]

select 
  a.billcode,
  b.payway,
  a.paymoney
from
  (select billcode,sum(paymoney) as paymoney from tb group by billcode) a,
  (select billcode,payway from tb t where not exists(select 1 from tb where billcode=t.billcode and paymoney>t.paymoney) b
where
  a.billcode=b.billcode

[lgx0914]

create table #tb (billcode  varchar(10),  payway  varchar(10) ,  paymoney money)
insert into  #tb values('09030006','预留款',50000)
insert into  #tb values('09030006','现金',50000)
insert into  #tb values('09030006','信用卡',120000)
insert into  #tb values('09030003','现金',305708)
insert into  #tb values('09030003','信用卡',1000000) 

select b.billcode,Sumpaymoney,a.payway
from (select billcode,max(paymoney) as paymoney,sum(paymoney) Sumpaymoney from #tb  group by billcode) b,#tb a
where a.paymoney=b.paymoney and a.billcode=b.billcode

[sdhdy]

select billcode, payway,sumPaymoney=(select sum(paymoney) from tb where billcode=a.billcode) from tb a 
where not exists(select 1 from tb where billcode=a.billcode and paymoney>a.paymoney)

[sdhdy]

create table tb 
(billcode varchar(20)   
,payway  nvarchar(10) 
,paymoney numeric(10,2))

insert into tb select 
'09030006',    N'预留款',     50000.00
insert into tb select 
'09030006',    N'现金',     50000.00
insert into tb select 
'09030006',    N'信用卡',     120000.00
insert into tb select 
'09030003',    N'现金',     305708.92
insert into tb select 
'09030003',    N'信用卡',     1000000.00

select billcode, payway,sumPaymoney=(select sum(paymoney) from tb where billcode=a.billcode) from tb a 
where not exists(select 1 from tb where billcode=a.billcode and paymoney>a.paymoney)

drop table tb
/*

billcode             payway     sumPaymoney                              
-------------------- ---------- ---------------------------------------- 
09030006             信用卡        220000.00
09030003             信用卡        1305708.92

（所影响的行数为 2 行）
*/

[亥亥]

select * from tx
where billcode in (select billcode from tx group by billcode)
and paymoney in (select max(paymoney) from tx group by billcode)

[taoistong]

create table tb 
(billcode varchar(20)   
,payway  nvarchar(10) 
,paymoney numeric(10,2))

insert into tb select 
'09030006',    N'预留款',     50000.00
insert into tb select 
'09030006',    N'现金',     50000.00
insert into tb select 
'09030006',    N'信用卡',     120000.00
insert into tb select 
'09030003',    N'现金',     305708.92
insert into tb select 
'09030003',    N'信用卡',     1000000.00

select billcode
,(select top 1 payway from tb c where b.billcode=c.billcode  order by paymoney desc) mainPayway
,sum(paymoney) sumPaymoney 
from tb b
group by billcode