LEFT JOIN时保留NULL值的查询及Stimulsoft报表两列输出需求

嘿，我懂你现在的需求——要把符合条件的Manager数据拆成两列展示到Stimulsoft报表里，而且不管对应计数是不是NULL，所有Manager记录都得完整显示对吧？看你已经写了部分临时表的代码，那咱们把它补全，用更清晰的方式实现这个需求：

核心思路 #

1. 先筛选出所有需要展示的Manager（AMCurrent=1且OmitInReport=0），给每条记录生成连续行号
2. 把行号为奇数的记录作为左列，偶数的作为右列，通过分组ID关联，保证两列一一对应
3. 用FULL JOIN确保哪怕某一组只有左/右列数据，也不会丢失任何Manager记录；如果需要统计计数，用LEFT JOIN保证NULL计数也能保留

完整SQL代码（不含计数版） #

-- 第一步：获取所有符合条件的Manager并生成行号
WITH ManagerList AS (
    SELECT 
        AMID, 
        AMName,
        ROW_NUMBER() OVER (ORDER BY AMName) AS RowNum -- 可替换成你需要的排序字段（比如AMID）
    FROM AM 
    WHERE AMCurrent = 1 AND OmitInReport = 0
)
-- 第二步：拆分奇数/偶数行关联成两列
SELECT 
    m_left.AMID AS LeftAMID,
    m_left.AMName AS LeftAMName,
    m_right.AMID AS RightAMID,
    m_right.AMName AS RightAMName
FROM 
    -- 奇数行作为左列，GroupID用于关联右列
    (SELECT *, (RowNum + 1) / 2 AS GroupID FROM ManagerList WHERE RowNum % 2 = 1) m_left
FULL JOIN 
    -- 偶数行作为右列，GroupID和左列对应
    (SELECT *, RowNum / 2 AS GroupID FROM ManagerList WHERE RowNum % 2 = 0) m_right
ON m_left.GroupID = m_right.GroupID
ORDER BY m_left.GroupID;

带计数统计的版本（保留NULL计数） #

如果需要同时展示每个Manager的关联计数（哪怕计数为NULL也要显示Manager），可以在CTE里加入LEFT JOIN统计：

WITH ManagerList AS (
    SELECT 
        a.AMID, 
        a.AMName,
        -- 替换成你实际要统计的关联表和字段，LEFT JOIN保证计数为NULL时仍保留Manager
        COUNT(b.RelatedID) AS ManagerRelatedCount,
        ROW_NUMBER() OVER (ORDER BY a.AMName) AS RowNum
    FROM AM a
    LEFT JOIN YourRelatedTable b ON a.AMID = b.ManagerID -- 替换成你的关联逻辑
    WHERE a.AMCurrent = 1 AND a.OmitInReport = 0
    GROUP BY a.AMID, a.AMName
)
-- 后续拆分两列的逻辑和上面一致
SELECT 
    m_left.AMID AS LeftAMID,
    m_left.AMName AS LeftAMName,
    m_left.ManagerRelatedCount AS LeftCount,
    m_right.AMID AS RightAMID,
    m_right.AMName AS RightAMName,
    m_right.ManagerRelatedCount AS RightCount
FROM 
    (SELECT *, (RowNum + 1) / 2 AS GroupID FROM ManagerList WHERE RowNum % 2 = 1) m_left
FULL JOIN 
    (SELECT *, RowNum / 2 AS GroupID FROM ManagerList WHERE RowNum % 2 = 0) m_right
ON m_left.GroupID = m_right.GroupID
ORDER BY m_left.GroupID;

导入Stimulsoft的注意事项 #

把这个查询结果作为报表数据源后，直接在报表设计器里拖入两个列控件，分别绑定LeftAMName/LeftCount和RightAMName/RightCount即可实现两列布局，所有Manager都会完整显示，哪怕某一列对应位置没有数据（你可以在报表里设置NULL显示为0或空字符串）。

内容的提问来源于stack exchange，提问作者Owain Esau