将表1的总数量减去表2(Subtracting the total quantity of table 1 to table 2)
伙计们通过按其CONTAINER_TYPE和CUSTOMER_NAME分组的TOTAL_QUANTITY减去两个表( Deliveries和Returns )时TOTAL_QUANTITY此问题,如果我减去例如5 - 0 ,则此示例查询返回Customer_Balance列,但如果我减去5 -1它返回4 。
select TOTAL_DELIVERY.CUSTOMER_NAME, TOTAL_DELIVERY.CONTAINER_TYPE, coalesce(TOTAL_DELIVERY.TOTAL_QUANTITY, 0) as TOTAL_DELIVERY, coalesce(TOTAL_PULLOUT.TOTAL_QUANTITY, 0) as TOTAL_PULLOUT, coalesce((TOTAL_DELIVERY.TOTAL_QUANTITY - TOTAL_PULLOUT.TOTAL_QUANTITY), 0) as CUSTOMER_BALANCE from TOTAL_DELIVERY left join TOTAL_PULLOUT on TOTAL_DELIVERY.CUSTOMER_NAME = TOTAL_PULLOUT.CUSTOMER_NAME and TOTAL_DELIVERY.CONTAINER_TYPE = TOTAL_PULLOUT.CONTAINER_TYPE Deliveries Table (View) ================ ---------------------------------------------- CUSTOMER_NAME| CONTAINER_TYPE | TOTAL_QUANTITY -------------+----------------+--------------- Bryan | Slim | 5 -------------+----------------+--------------- Bryan | Jug | 5 -------------+----------------+--------------- Returns Table (View) ============= CUSTOMER_NAME| CONTAINER_TYPE| TOTAL_QUANTITY -------------+---------------+--------------- Bryan | Slim | 5 -------------+---------------+--------------- Expected output =============== Customer | Container | Total_Delivery | Total_Return | Customer_Balance | ---------+-----------+----------------+--------------+------------------ Bryan | Slim | 5 | 5 | 0 | ---------+-----------+----------------+--------------+------------------ Bryan | Jug | 5 | 0 | 5 | ---------+-----------+----------------+--------------------------------- My INCORRECT Result output (incorrect result at the bottom right cell) ========= Customer | Container | Total_Delivery | Total_Return | Customer_Balance | ---------+-----------+----------------+--------------+------------------ Bryan | Slim | 5 | 5 | 0 | ---------+-----------+----------------+--------------+------------------ Bryan | Jug | 5 | 0 | 0 | ---------+-----------+----------------+---------------------------------你能告诉我我做错了什么吗? 谢谢!
Guys i have this problem on subtracting the the two tables (Deliveries and Returns) by its TOTAL_QUANTITY grouped by their CONTAINER_TYPE and CUSTOMER_NAME, this sample query returns the column Customer_Balance of 0 if I subtracted for example 5 - 0, but if i subtracted 5 -1 it returns 4.
select TOTAL_DELIVERY.CUSTOMER_NAME, TOTAL_DELIVERY.CONTAINER_TYPE, coalesce(TOTAL_DELIVERY.TOTAL_QUANTITY, 0) as TOTAL_DELIVERY, coalesce(TOTAL_PULLOUT.TOTAL_QUANTITY, 0) as TOTAL_PULLOUT, coalesce((TOTAL_DELIVERY.TOTAL_QUANTITY - TOTAL_PULLOUT.TOTAL_QUANTITY), 0) as CUSTOMER_BALANCE from TOTAL_DELIVERY left join TOTAL_PULLOUT on TOTAL_DELIVERY.CUSTOMER_NAME = TOTAL_PULLOUT.CUSTOMER_NAME and TOTAL_DELIVERY.CONTAINER_TYPE = TOTAL_PULLOUT.CONTAINER_TYPE Deliveries Table (View) ================ ---------------------------------------------- CUSTOMER_NAME| CONTAINER_TYPE | TOTAL_QUANTITY -------------+----------------+--------------- Bryan | Slim | 5 -------------+----------------+--------------- Bryan | Jug | 5 -------------+----------------+--------------- Returns Table (View) ============= CUSTOMER_NAME| CONTAINER_TYPE| TOTAL_QUANTITY -------------+---------------+--------------- Bryan | Slim | 5 -------------+---------------+--------------- Expected output =============== Customer | Container | Total_Delivery | Total_Return | Customer_Balance | ---------+-----------+----------------+--------------+------------------ Bryan | Slim | 5 | 5 | 0 | ---------+-----------+----------------+--------------+------------------ Bryan | Jug | 5 | 0 | 5 | ---------+-----------+----------------+--------------------------------- My INCORRECT Result output (incorrect result at the bottom right cell) ========= Customer | Container | Total_Delivery | Total_Return | Customer_Balance | ---------+-----------+----------------+--------------+------------------ Bryan | Slim | 5 | 5 | 0 | ---------+-----------+----------------+--------------+------------------ Bryan | Jug | 5 | 0 | 0 | ---------+-----------+----------------+---------------------------------can you please tell me what am i doing wrong? thank you!
最满意答案
(TOTAL_DELIVERY.TOTAL_QUANTITY - TOTAL_PULLOUT.TOTAL_QUANTITY)
一定是
(coalesce(TOTAL_DELIVERY.TOTAL_QUANTITY,0) - coalesce(TOTAL_PULLOUT.TOTAL_QUANTITY, 0))(TOTAL_DELIVERY.TOTAL_QUANTITY - TOTAL_PULLOUT.TOTAL_QUANTITY)
must be
(coalesce(TOTAL_DELIVERY.TOTAL_QUANTITY,0) - coalesce(TOTAL_PULLOUT.TOTAL_QUANTITY, 0))更多推荐
发布评论