用sql语句处理下列问题,求大佬解答!!

2021-02-20 12:25发布

SELECT   供应商代码 = vv.vend_id   ,供应商名称 = max(vv.vend_name)  ,产品代码 = pp.prod_id  ,产品名称 = max(pp.prod_name),   订单数量 = sum(dd.quantity)  ,订单价格 = sum(dd.item_price)  ,订单金额 = sum(dd.quantity*dd.item_price)  ,采购数量 = (case WHEN order_TYPE='IMP' THEN SUM(DD.QUANTITY) ELSE 0 END)  ,采购单价 = (case WHEN order_TYPE='IMP' THEN SUM(item_price)ELSE 0 END)  ,采购金额 = (case WHEN order_TYPE='IMP' THEN SUM(item_price)ELSE 0 END)*(case WHEN order_TYPE='IMP' THEN SUM(DD.QUANTITY) ELSE 0 END)  ,销售数量 = (case WHEN order_TYPE='EXP' THEN SUM(DD.QUANTITY) ELSE 0 END)  ,销售价格 = (case WHEN order_TYPE='exp' THEN SUM(item_price)ELSE 0 END)  ,销售金额 = (case WHEN order_TYPE='EXP' THEN SUM(DD.QUANTITY) ELSE 0 END)*(case WHEN order_TYPE='exp' THEN SUM(item_price)ELSE 0 END)  ,库存数量 = (case WHEN order_TYPE='IMP' THEN SUM(DD.QUANTITY) ELSE 0 END)-(case WHEN order_TYPE='EXP' THEN SUM(DD.QUANTITY) ELSE 0 END)  ,库存余额 = ((case WHEN order_TYPE='IMP' THEN SUM(DD.QUANTITY) ELSE 0 END)-(case WHEN order_TYPE='EXP' THEN SUM(DD.QUANTITY) ELSE 0 END))*(case WHEN order_TYPE='IMP' THEN SUM(item_price)ELSE 0 END) FROM Vendors vv   LEFT JOIN Products pp ON pp.vend_id=vv.vend_id  LEFT JOIN OrderItems dd ON dd.prod_id=pp.prod_id   LEFT JOIN Orders ON dd.order_num=Orders.order_num  LEFT JOIN Customers ON Orders.cust_id=Customers.cust_id WHERE vv.vend_id='DLL01'  GROUP BY vv.vend_id ,pp.prod_id ,order_TYPE 

 

结果如下

    我要把里面的0都去掉,合并成四行,该怎么做,求大佬解答!

10条回答
码农翻身
1楼-- · 2021-02-20 12:55

您好,主要问题在于分组,仔细核实一下分组。

Remote Sensing_
2楼-- · 2021-02-20 12:58

您好,如有疑问可以给我留言

凌波微肚
3楼-- · 2021-02-20 13:00

 

SCE林木夕
4楼-- · 2021-02-20 13:00