kscdsc
kscdsc
2021-01-26 10:17

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

  • jar

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都去掉,合并成四行,该怎么做,求大佬解答!

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

11条回答