代码之家 › 专栏 › 技术社区 › Andre

两侧缺少行的自连接

sql-server sql

Andre · 技术社区 · 9 年前

我有一张桌子,里面有加入和离开组织的人。

样本数据 : Sql Fiddle

| pId |     pName |  Unit | YearIn | YearOut |
|-----|-----------|-------|--------|---------|
|   1 |      Noah | North |   2013 |  (null) |
|   2 |      Liam | North |   2013 |    2014 |
|   3 |     Mason | North |   2013 |    2014 |
|   4 |     Jacob | North |   2000 |    2014 |
|   5 |   William |  West |   2000 |    2013 |
|   6 |     Ethan |  West |   2013 |    2013 |
|   7 |   Michael |  West |   2014 |    2015 |
|   8 | Alexander |  West |   2013 |  (null) |

多年>=2013年我想得到参赛人数和离开人数,按年份和单位分组。

期望的输出 (我不确定包含完全不存在数据的行是更容易还是更难):

| Year |  Unit |  NumIn | NumOut |
|------|-------|--------|--------|
| 2013 | North |      3 | (null) |
| 2013 |  West |      2 |      2 |
| 2014 | North | (null) |      3 |
| 2014 |  West |      1 | (null) |
| 2015 | North | (null) | (null) |      (optional)
| 2015 |  West | (null) |      1 |

为了让NumIn和NumOut排在一行,我想我需要某种自我加入。
我试过了 :

SELECT p1.Year, p1.Unit, p1.NumIn, p2.NumOut FROM 
(
    SELECT YearIn AS Year, Unit, COUNT(pId) AS NumIn
    FROM People
    WHERE YearIn >= 2013
    GROUP BY YearIn, Unit
) p1
LEFT JOIN
(
    SELECT YearOut AS Year, Unit, COUNT(pId) AS NumOut
    FROM People
    WHERE YearOut >= 2013
    GROUP BY YearOut, Unit
) p2 
    ON p1.Year = p2.Year AND p1.Unit = p2.Unit

但当然,这只给了我左侧有数据的行。

| Year |  Unit | NumIn | NumOut |
|------|-------|-------|--------|
| 2013 | North |     3 | (null) |
| 2013 |  West |     2 |      2 |
| 2014 |  West |     1 | (null) |

结果将输入Excel中的数据透视表。所以我“作弊”,使用一个简单的UNION查询来获取所有数据:

SELECT YearIn AS Year, Unit, COUNT(pId) AS NumIn, NULL AS NumOut
FROM People
WHERE YearIn >= 2013
GROUP BY YearIn, Unit

UNION ALL

SELECT YearOut AS Year, Unit, NULL AS NumIn, COUNT(pId) AS NumOut
FROM People
WHERE YearOut >= 2013
GROUP BY YearOut, Unit

ORDER BY Year, Unit;

结果:

| Year |  Unit |  NumIn | NumOut |
|------|-------|--------|--------|
| 2013 | North |      3 | (null) |
| 2013 |  West | (null) |      2 |
| 2013 |  West |      2 | (null) |
| 2014 | North | (null) |      3 |
| 2014 |  West |      1 | (null) |
| 2015 |  West | (null) |      1 |

这可以作为透视表的基础,但我想知道如何在SqlServer中获得所需的结果。

我使用的是Sql Server 2008 R2,以备不时之需。
谢谢

2 回复 | 直到 9 年前

Gordon Linoff 9 年前

我认为您需要一个子查询和聚合:

SELECT Year, Unit, SUM(NumIn) as NumIn, SUM(NumOut) as NumOut
FROM (SELECT YearIn AS Year, Unit, COUNT(pId) AS NumIn, NULL AS NumOut
      FROM People
      WHERE YearIn >= 2013
      GROUP BY YearIn, Unit
      UNION ALL
      SELECT YearOut AS Year, Unit, NULL AS NumIn, COUNT(pId) AS NumOut
      FROM People
      WHERE YearOut >= 2013
      GROUP BY YearOut, Unit
     ) yu
GROUP BY Year, Unit;

Gottfried Lesigang 9 年前

这应该是有效的:

WITH Years(y) AS
(
    SELECT * FROM (VALUES('2013'),('2014'),('2015') ) AS tbl(y)
)
,Units AS 
(
    SELECT DISTINCT Unit FROM People
)
SELECT *
FROM Years CROSS JOIN Units
CROSS APPLY(SELECT (SELECT COUNT(*) FROM People AS inP WHERE inP.Unit=Units.Unit AND Years.y=inP.YearIn) AS NumIn
                  ,(SELECT COUNT(*) FROM People AS outP WHERE outP.Unit=units.Unit AND Years.y=outP.YearOut ) As NumOut
) AS Counts
ORDER BY Years.y,Unit