有这样一个表AAA
USER(客户)---PN(物料号)---QTY(销售的数量)
O-10 306-077A 757
O-16 306-077A 127
G-42 306-077A 20
B-40 306-179 12
F-58 306-179 120
H-23 306-224 50
........
求查询出物料销售量最大的客户,如下表。万分感谢！
USER(客户)---PN(物料号)---QTY(销售的数量)
O-10 306-077A 757
F-58 306-179 120
H-23 306-224 50
........
------解决方案--------------------
- SQL code
select * from AAA a where not exists (select 1 from AAA where PN=a.PN and QTY>a.QTY)
------解决方案--------------------
- SQL code
select * from aaa t where qty=(select max(qty) from tb where pn=t.pn)
------解决方案--------------------
- SQL code
select * from aaa a where not exists(select 1 from aaa where PN=a.PN and QTY>a.QTY)
------解决方案--------------------
- SQL code
create table AAA([USER] varchar(10),PN varchar(20),QTY int)insert into AAA select 'O-10','306-077A',757insert into AAA select 'O-16','306-077A',127insert into AAA select 'G-42','306-077A',20insert into AAA select 'B-40','306-179',12insert into AAA select 'F-58','306-179',120insert into AAA select 'H-23','306-224',50goselect * from AAA a where not exists(select 1 from AAA where PN=a.PN and QTY>a.QTY)/*如果同一客户采购不止一笔,则要先统计后再比较:;with cte as(select [USER],PN,sum(QTY)as QTY from tb group by [USER],PN)select * from cte a where not exists(select 1 from cte where PN=a.PN and QTY>a.QTY)*/godrop table AAA/*USER PN QTY---------- -------------------- -----------O-10 306-077A 757F-58 306-179 120H-23 306-224 50(3 行受影响)*/