如何在case语句中进行子查询？

布拉德·德克

这是我的编码

select item_number[Item Number],    
            SUM(CASE WHEN [type] = 0 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%')    THEN actual_qty ELSE 0 END) AS [DC C1]  
            ,SUM(CASE WHEN [type] = -6 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%')  THEN actual_qty ELSE 0 END) AS [DC C7]  
            ,SUM(CASE WHEN [type] = -9 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%')  THEN actual_qty ELSE 0 END) AS [DC C1lock]          
            ,SUM(CASE WHEN [type] = -15 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%') THEN actual_qty ELSE 0 END) AS [DC C7lock]
            ,SUM(CASE WHEN [type] = 0 and location_id like 'GW%'    THEN actual_qty ELSE 0 END) AS [GW C1]          
            ,SUM(CASE WHEN [type] = -6 and location_id like 'GW%'   THEN actual_qty ELSE 0 END) AS [GW C1]      
            ,SUM(CASE WHEN [type] = -9 and location_id like 'GW%'   THEN actual_qty ELSE 0 END) AS [GW C1lock]      
            ,SUM(CASE WHEN [type] = -15 and location_id like 'GW%'  THEN actual_qty ELSE 0 END) AS [GW C7lock]
            ,SUM(CASE WHEN [type] = 0 and location_id like 'SW%'    THEN actual_qty ELSE 0 END) AS [SW C1]          
            ,SUM(CASE WHEN [type] = -6 and location_id like 'SW%'   THEN actual_qty ELSE 0 END) AS [SW C1]      
            ,SUM(CASE WHEN [type] = -9 and location_id like 'SW%'   THEN actual_qty ELSE 0 END) AS [SW C1lock]      
            ,SUM(CASE WHEN [type] = -15 and location_id like 'SW%'  THEN actual_qty ELSE 0 END) AS [SW C7lock]  
From t_stored_item with(nolock)         
group by item_number

我得到的错误代码是

消息130，级别15，状态1，第3行无法对包含聚集或子查询的表达式执行聚集功能。

消息130，级别15，状态1，第5行无法对包含聚集或子查询的表达式执行聚集功能。

消息130，级别15，状态1，第7行无法对包含聚集或子查询的表达式执行聚集功能。

消息130，级别15，状态1，第9行无法对包含聚集或子查询的表达式执行聚集功能。

现在，我知道其余的代码很好，但是我现在遇到的唯一问题是由于这样编写子查询。我最终希望拥有的仓库中的所有库存都可以，但是我希望它们被不同的仓库分开。我们将前体仓库的名称命名为GW和SW，其余的就在那儿，因为主仓库中有许多不同的走道，但那里没有真正的常数可供我参考。自己写的查询效果很好

select location_id 
from t_location with(nolock) 
where location_id like 'GW%' or location_id like 'SW%'

但是，当嵌套到case语句中并对其求和时，它并不想撤回任何结果...末尾的格式为每个物料编号提供一行，但为不同的仓库提供不同的列，这就是我的方式想要查看信息，因为它比跳转到不同的行更容易阅读。

反正有什么可以做到这一点？分组需要保留下来，以便一切都可以撤回，但我没有想出一种简单的方法来获取信息。

蒂姆·比格莱森（Tim Biegeleisen）

将联接与条件聚合一起使用：

SELECT
    si.item_number,
    SUM(CASE WHEN si.[type] = 0 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C1],
    SUM(CASE WHEN si.[type] = -6 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C7],
    SUM(CASE WHEN si.[type] = -9 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C1lock],
    SUM(CASE WHEN si.[type] = -15 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C7lock],
    SUM(CASE WHEN si.[type] = 0 AND l.location_id LIKE 'GW%'
            THEN l.actual_qty ELSE 0 END) AS [GW C1],
    ...
FROM t_stored_item si
LEFT JOIN t_location l
    ON si.location_id = l.location_id
GROUP BY
    si.item_number;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。