mysql - SQL 报告每个供应商、每种木材类型和等级

Question

我想为每个供应商制作一份库存报告，按木材类型和等级分组。

我当前的 SQL 查询:

    SELECT ll.supplier, 
       obd.wood_type_output_breakdown                                AS 
       wood_type, 
       obp.grade_output_breakdown_panjang                            AS grade, 
       (SELECT Sum(xobp.volume_breakdown_panjang) 
        FROM   output_breakdown_panjang xobp 
        WHERE  xobp.id_output_breakdown = obd.id_output_breakdown 
               AND 
       xobp.grade_output_breakdown_panjang = obp.grade_output_breakdown_panjang 
               AND xobp.id_output_breakdown = ob.id_output_breakdown 
               AND ob.id_output_breakdown = obd.id_output_breakdown) AS volume 
FROM   loglist ll 
       LEFT JOIN trimming t 
              ON t.id_loglist = ll.id_loglist 
       LEFT JOIN breakdown b 
              ON b.id_trimming = t.id_trimming 
       LEFT JOIN output_breakdown_detil det 
              ON det.id_breakdown = b.id_breakdown 
       LEFT JOIN output_breakdown ob 
              ON ob.id_output_breakdown = det.id_output_breakdown, 
       output_breakdown obd, 
       output_breakdown_panjang obp 
GROUP  BY Concat(ll.supplier, obd.wood_type_output_breakdown, 
                    obp.grade_output_breakdown_panjang) 
ORDER  BY ll.supplier, 
          obd.wood_type_output_breakdown 

但结果是这样的:

应该是这样的:

这里我附上sql供您帮助:http://s000.tinyupload.com/index.php?file_id=07777439563611215580

有人可以帮忙吗？

Answer 1

我终于找到了解决方案:

> SELECT l.supplier, h.jenis_kayu_output_breakdown as jenis_kayu, g.grade_output_breakdown_panjang as grade,
SUM(distinct g.volume_breakdown_panjang) as volume
FROM output_breakdown_panjang as g
LEFT JOIN output_breakdown AS h ON h.id_output_breakdown = g.id_output_breakdown
LEFT JOIN output_breakdown_detil as i ON i.id_output_breakdown = g.id_output_breakdown
LEFT JOIN breakdown as j ON j.id_breakdown = i.id_breakdown
LEFT JOIN trimming as k ON k.id_trimming = j.id_trimming
LEFT JOIN loglist as l on l.id_loglist = k.id_loglist
group by CONCAT(l.supplier,h.jenis_kayu_output_breakdown,g.grade_output_breakdown_panjang)