mysql - SELECT 字段 GROUP BY max(field) 和 GROUP BY 其他字段 MySQL

Question

I have record like this pic, PLEASE CLICK TO SHOW PIC

how to get like this pic, PLEASE CLICK TO SHOW PIC

创建表查询:

create table lap ( no_faktur varchar(30) primary key, tgl_jual date, kd_barang varchar(20), nm_barang varchar(100), harga_beli int (20), harga_jual int(20), jum_jual int(10) );

插入表查询:

insert into lap values ('FJ-2013001','2013-10-01','B-001','laptop Apple','500000','700000','2'); insert into lap values ('FJ-2013002','2012-10-01','B-002','laptop Apple','300000','500000','4'); insert into lap values ('FJ-2013003','2013-10-03','B-001','laptop Apple','500000','700000','1'); insert into lap values ('FJ-2013004','2013-10-11','B-003','HDD 500 GB','700000','800000','1'); insert into lap values ('FJ-2013005','2012-10-01','B-001','laptop Apple','500000','700000','3'); insert into lap values ('FJ-2013006','2013-10-04','B-004','MONITOR 14"','100000','800000','2'); insert into lap values ('FJ-2013007','2013-02-01','B-001','laptop Apple','700000','850000','4'); insert into lap values ('FJ-2013008','2013-10-21','B-005','HDD 500 GB','800000','900000','3'); insert into lap values ('FJ-2013009','2013-10-27','B-001','laptop Apple','500000','700000','1');

我是这样想的:

select no_faktur,tgl_jual,kd_barang,nm_barang,harga_jual,jum_jual,sum(harga_jual*jum_jual) as total_jual from lap where month(tgl_jual)='10' and year(tgl_jual)='2013' and jum_jual=(select max(jum_jual) from lap group by kd_barang) order by total_jual desc;

但是不起作用，因为 *jum_jual 超过 1 行。

请帮忙，先谢谢了

Answer 1

选择no_faktur,tgl_jual,kd_barang,nm_barang,harga_jual,sum(jum_jual)作为jum_jual,sum(harga_jual*jum_jual)作为total_jual 从一圈开始，其中月份(tgl_jual)='10'和年份(tgl_jual)='2013' 按 kd_barang 分组 按total_jual desc排序；