时间戳

首页 > 解决方案 > 即使所有其他人都没有返回数据，也显示第一列

sql - 即使所有其他人都没有返回数据，也显示第一列

问题描述

我需要显示第一列数据，即使剩余的数据列在结果中没有值。

我尝试了各种功能来达到我的结果，但没有取得任何进展。 

SELECT 
  Distinct dbo.ae_i_inv_e.cycle_code AS Section,
  Sum(dbo.ae_i_loc_e.qty) As Quantity_On_Hand,
  Sum(dbo.ae_i_inv_w.whse_cost) AS Average_Cost,
  SUM (dbo.ae_i_loc_e.qty * dbo.ae_i_inv_w.whse_cost) AS Cost_On_Hand,
  Count (dbo.ae_i_inv_e.part) AS Items


  FROM
(  dbo.ae_i_inv_e
  Left Outer JOIN dbo.ae_i_loc_e ON (dbo.ae_i_inv_e.multitenant_id = 
dbo.ae_i_loc_e.multitenant_id)
  AND (dbo.ae_i_inv_e.part = dbo.ae_i_loc_e.part))
  Left Outer JOIN dbo.ae_i_inv_w ON (dbo.ae_i_loc_e.multitenant_id = 
dbo.ae_i_inv_w.multitenant_id)
  AND (dbo.ae_i_inv_w.multitenant_id = dbo.ae_i_inv_e.multitenant_id)
  AND (dbo.ae_i_loc_e.part = dbo.ae_i_inv_w.part)
  AND (dbo.ae_i_inv_w.part = dbo.ae_i_inv_e.part)

WHERE dbo.ae_i_inv_e.multitenant_id = '2824' AND
    dbo.ae_i_inv_e.active = 'N'

GROUP BY
  dbo.ae_i_inv_e.cycle_code

ORDER BY
  dbo.ae_i_inv_e.cycle_code

实际结果部分 Quantity_on_hand Average_cost Cost_on_hand 项目

预期成绩

Section  Quantity_on_hand  Average_cost  Cost_on_hand   Items
A           0                 0               0           0
B           0                 0               0           0
C           0                 0               0           0
D           0                 0               0           0
E           0                 0               0           0

标签： sql

解决方案

SELECT
    a.Section,
    ISNULL(COALESCE(a.Quantity_On_Hand, b.Quantity_On_Hand), 0) as Quantity_On_Hand,
    ISNULL(COALESCE(a.cost_on_hand, b.cost_on_hand), 0) as cost_on_hand,
    ISNULL(COALESCE(a.items, b.items), 0) as items

FROM (
    Select *
    FROM (
    VALUES 
        ('A', NULL, NULL, NULL), 
        ('B', NULL, NULL, NULL), 
        ('C', NULL, NULL, NULL), 
        ('E', NULL, NULL, NULL), 
        ('G', NULL, NULL, NULL), 
        ('N', NULL, NULL, NULL), 
        ('P', NULL, NULL, NULL), 
        ('S', NULL, NULL, NULL), 
        ('T', NULL, NULL, NULL), 
        ('U', NULL, NULL, NULL), 
        ('V', NULL, NULL, NULL)
    ) inv(Section, Quantity_On_Hand, cost_on_hand, items)
) AS a
FULL OUTER JOIN (
    SELECT 
        e.cycle_code AS Section,
        isnull(Sum (loc.qty),0) As Quantity_On_Hand,
        isnull(SUM (loc.qty * w.whse_cost),0) AS Cost_On_Hand,
        Count (e.part) AS Items

    FROM 
        ae_i_inv_e e

        LEFT OUTER JOIN ae_i_loc_e loc ON 
        e.multitenant_id = loc.multitenant_id AND 
        e.part = loc.part

        LEFT OUTER JOIN ae_i_inv_w w ON 
        e.multitenant_id = w.multitenant_id AND 
        e.part = w.part 

    WHERE 
        e.active = 'N' AND
        e.multitenant_id = '2824'

    GROUP BY
        e.cycle_code)
AS b ON a.section = b.Section

Order By
    a.section

这解决了我遇到的问题。

推荐阅读