华兆林产品销售分析页SQL

select *from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId a是主表，b是子表；以 left join左边的表为准，去匹配右边的表，字段匹配不上，就为空

查询当前月份的发货情况 select * from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.latoId where a.shipType='销售发货' and a.status='已发货' and DATE_FORMAT(a.deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') a.shipType 发货类型；a.status 状态； a.deliveryDate 发货日期

b.formId=a.latoId 主从表关联，都是这2个字段

b.materialName 产品名称；b.weight 发货重量 select b.materialName,b.weight from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId where a.shipType='销售发货' and a.status='已发货' and DATE_FORMAT(a.deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m')

按b.materialName 产品名称 分组统计：出现 发货重量 字段 的产品计数

select b.materialName,count(b.weight) from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId 
where a.shipType='销售发货' and a.status='已发货' and 
DATE_FORMAT(a.deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName

按b.materialName 产品名称 分组统计： 发货重量 字段累加和 sumWeight

select b.materialName,sum(b.weight) as sumWeight from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId 
where a.shipType='销售发货' and a.status='已发货' and 
DATE_FORMAT(a.deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName

按b.materialName 产品名称 分组统计： 发货重量 字段累加和 sumWeight 并按sumWeight降序排序

select b.materialName,sum(b.weight) as sumWeight from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId 
where a.shipType='销售发货' and a.status='已发货' and 
DATE_FORMAT(a.deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName ORDER BY sumWeight DESC

对应UI

orderno 订单编号 按b.materialName 产品名称 分组统计：订单的个数，且订单编号去重

select b.materialName, count(DISTINCT a.orderno) as orderCount from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId where a.shipType='销售发货' and status='已发货' and DATE_FORMAT(deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName

b.weight 发货重量 按b.materialName 产品名称 分组统计

select b.materialName, count(DISTINCT a.orderno) as orderCount, sum(b.weight) as sellCount from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId where a.shipType='销售发货' and status='已发货' and DATE_FORMAT(deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName

amount 金额

select b.materialName, count(DISTINCT a.orderno) as orderCount, sum(b.weight) as sellCount, sum(b.amount) as sellPrice from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId where a.shipType='销售发货' and status='已发货' and DATE_FORMAT(deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName

排序

select b.materialName, count(DISTINCT a.orderno) as orderCount, sum(b.weight) as sellCount, sum(b.amount) as sellPrice from t10001_c0047_d01 b LEFT JOIN t10001_c0047 a on b.formId=a.formId where a.shipType='销售发货' and status='已发货' and DATE_FORMAT(deliveryDate,'%Y-%m')= DATE_FORMAT(CURDATE(),'%Y-%m') group by b.materialName ORDER BY sellPrice  desc

对应UI