sql-server - 使用 COALESCE FULL OUTER JOIN 复制行

Question

这是漫长的一天，也许这是一个简单的问题，但我还是被困住了。

基本上我有两个类似的表Sales和Forecasts。我正在尝试创建一个 View ，该 View 从两个表中选择行，并选择给定模型+月份+国家/地区的任何内容。如果两个表都包含数据，则 Sales 具有优先级，这意味着应省略 Forecast 行。

为了简化查询，我使用了 CTE。实际上，两个表的架构不同，并且许多表都连接在一起，而且 Forecasts 包含历史记录行，其中只应显示最后一个表。

我创建了一个简化的架构和数据来向您展示我正在尝试做什么:

WITH Sales AS
(
    SELECT 
        ID, Model, Month, Country,
        Amount              = Count,
        [Forecast / Sales]  = 'Sales'
    FROM dbo.Sales
)
, Forecasts AS
(
    SELECT 
        ID, Model, Month, Country,
        Amount              = Count,
        [Forecast / Sales]  = 'Forecast'
    FROM dbo.Forecast
)
SELECT  ID = COALESCE(s.ID, fc.ID), 
        Model = COALESCE(s.Model, fc.Model), 
        Month = COALESCE(s.Month, fc.Month),
        Country = COALESCE(s.Country, fc.Country),
        Amount = COALESCE(s.Amount, fc.Amount),
        [Forecast / Sales] = COALESCE(s.[Forecast / Sales], fc.[Forecast / Sales])
FROM Sales s
FULL OUTER  JOIN Forecasts fc 
    ON s.Model = fc.Model
        AND s.Month = fc.Month
        AND s.Country = fc.Country
ORDER BY ID,Month,Country,Model

这是一个包含示例数据的 sql-fiddle:http://sqlfiddle.com/#!3/9081b/9/2

结果:

ID  MODEL   MONTH   COUNTRY AMOUNT  FORECAST / SALES
1   ABC December, 01 2013 00:00:00+0000 Germany 777 Sales
2   ABC January, 01 2014 00:00:00+0000  Germany 999 Sales
3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales
3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales
4   ABC January, 01 2014 00:00:00+0000  UK  600 Forecast
4   ABC February, 01 2014 00:00:00+0000 UK  444 Sales
5   ABC March, 01 2014 00:00:00+0000    UK  500 Forecast

此查询根据 ID 和源(最后一列)返回重复项。

3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales
3   ABC February, 01 2014 00:00:00+0000 Germany 900 Sales

显然，对于该模型+月份+国家/地区组合，Sales 行被多个 Forecast 行重复。如果 Sales+Forecast 行不存在重复项，我如何仅获取 Sales 行；如果有 Forecast 行，我如何获取没有 Sales 行？

Answer 1

您的查询的问题不在于使用 COALESCE，而在于 JOIN 的使用。 Forecast 表中有 2 行具有相同的 Model、Month、Country 组合，其中 ID 2 和 3 的行:

╔════╦═══════╦═════════════════════════╦═════════╦═══════╗
║ ID ║ Model ║          Month          ║ Country ║ Count ║
╠════╬═══════╬═════════════════════════╬═════════╬═══════╣
║  2 ║ ABC   ║ 2014-02-01 00:00:00.000 ║ Germany ║  1100 ║
║  3 ║ ABC   ║ 2014-02-01 00:00:00.000 ║ Germany ║   900 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

它们都与 Sales 表中的 ID 3 行连接:

╔════╦═══════╦═════════════════════════╦═════════╦═══════╗
║ ID ║ Model ║          Month          ║ Country ║ Count ║
╠════╬═══════╬═════════════════════════╬═════════╬═══════╣
║  3 ║ ABC   ║ 2014-02-01 00:00:00.000 ║ Germany ║   900 ║
╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

由于您的查询使用的是 COALESCE(s.ID, fc.ID)，因此您会在结果中得到 ID 3 的 2 行