如何在多个有附加条件的表中进行选择？

Question

我的SQL Server数据库中有表“Table_Codes

Code | Description | Owner | Location
A202   SomeDescr1    Owner1       M-3
B301   SomeDescr2    Owner2       M-3
C898   SomeDescr3    Owner3       M-2
B744   SomeDescr4    Owner4       M-3
V123   SomeDescr5    Owner5       M-3         - code is not define in second table

我还有"Table_Stock“这张桌子

Code | Qty |     DC
A202    10       M-3
B301    12       M-2      !-not the same location like in 1st table for this code
C898    90       M-2
B744   120       M-3

我无法构建正确的查询来从此表中选择下一个结果：

    Code |    Qty(Location=DC) |  Qty on alternate DC (Location from 1st table <> DC)
    A202             10                       0
    B301              0                      12
    C898             90                       0
    B744            120                       0
    V123   Not in stock            Not in stock

我可以通过使用辅助表来实现。但是我想知道如何使用单选查询来做到这一点？

Answer 1

像这样的东西(SQL Fiddle)？

SELECT c.Code,
    CASE WHEN s.Code IS NULL THEN 'Not in stock' 
        ELSE CAST(SUM(CASE WHEN s.DC = c.Location THEN s.Qty ELSE 0 END) AS VARCHAR(20)) END,
    CASE WHEN s.Code IS NULL THEN 'Not in stock' 
        ELSE CAST(SUM(CASE WHEN s.DC <> c.Location THEN s.Qty ELSE 0 END) AS VARCHAR(20)) END
FROM Table_Codes c
LEFT OUTER JOIN Table_Stock s ON c.Code = s.Code
GROUP BY c.Code, s.Code

注在一个查询中执行此操作的唯一方法是将第二列和第三列转换为字符类型。根据您希望在查询的另一端执行的操作，您可能希望考虑返回NULL而不是'Not in stock‘。