有如下表:ITEM_CODE QTY UNIT UPRICE
0001 10 PCS 1.5
0001 25 PCS 3.5
0001 10 PCS 2.0
0002 20 PCS 7.0
0003 40 PCS 4.0
0003 50 PCS 5.0我想要的结果:ITEM_CODE相同的QTY加总,UPRICE取最大值ITEM_CODE QTY UNIT UPRICE
0001 45 PCS 3.5
0002 20 PCS 7.0
0003 90 PCS 5.0
数据库环境:sql 2000
0001 10 PCS 1.5
0001 25 PCS 3.5
0001 10 PCS 2.0
0002 20 PCS 7.0
0003 40 PCS 4.0
0003 50 PCS 5.0我想要的结果:ITEM_CODE相同的QTY加总,UPRICE取最大值ITEM_CODE QTY UNIT UPRICE
0001 45 PCS 3.5
0002 20 PCS 7.0
0003 90 PCS 5.0
数据库环境:sql 2000
select ITEM_CODE,sum(QTY) QTY,max(UNIT) UNIT,max(UPRICE) UPRICE
from tb
group by ITEM_CODE
--or
select ITEM_CODE,sum(QTY) QTY,UNIT,max(UPRICE) UPRICE
from tb
group by ITEM_CODE,UNIT
TableName你的数据表名
Declare @T Table(
ITEM_CODE Varchar(10),
QTY Int,
UNIT Varchar(10),
UPRICE Decimal(8,1)
)InSert Into @T
Select '0001', 10, 'PCS', 1.5
Union
Select '0001', 25, 'PCS', 3.5
Union
Select '0001', 10, 'PCS', 2.0
Union
Select '0002', 20, 'PCS', 7.0
Union
Select '0003', 40, 'PCS', 4.0
Union
Select '0003', 50, 'PCS', 5.0Select ITEM_CODE, Sum(QTY) AS QTY, UNIT, Max(UPRICE) AS UPRICE
From @T
Group By ITEM_CODE, UNIT