Дублирование строк при объединении трех таблиц

Я использую SQL Server 2014, и у меня возникла проблема с запросом. У меня три стола. Report состоит из десяти ClothingObservation и HygieneObservation. Я делаю это, ссылаясь на ReportId из Report в десяти строках каждого из двух типов наблюдений, всего 20 наблюдений на отчет. Я хочу выбрать все строки одного отчета. Когда я пытаюсь это сделать, я получаю 100 строк. Моя цель — получить 10 строк или 20 строк со значениями NULL. На данный момент это сделано для тестирования, поэтому Report содержит только 1 строку, а ClothingObservation и HygieneObservation содержат по 10 строк каждая, и все они ссылаются на ReportId одного существующего отчета.

Мои таблицы, детали опущены для ясности:

CREATE TABLE HygieneObservation
(
    HygieneObservationId int PRIMARY KEY IDENTITY NOT NULL,
    ...
    ReportId int NOT NULL
)

CREATE TABLE ClothingObservation
(
    ClothingObservationId int PRIMARY KEY IDENTITY NOT NULL,
    ...
    ReportId int NOT NULL
)

CREATE TABLE Report
(
    ReportId int PRIMARY KEY IDENTITY NOT NULL,
    Period Date NOT NULL,
    Reporter nvarchar(8) NOT NULL,
    DepartmentId int NOT NULL
)

Мой запрос:

SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 

    ClothingObservation.ClothingObservationId,
    HygieneObservation.HygieneObservationId

FROM Report

LEFT JOIN ClothingObservation ON
    (ClothingObservation.ReportId = Report.ReportId)
LEFT JOIN HygieneObservation ON
    (HygieneObservation.ReportId = Report.ReportId)

GROUP BY
    Report.ReportId,
    Period,
    Reporter,
    DepartmentId,

    ClothingObservation.ClothingObservationId,
    HygieneObservation.HygieneObservationId

Это дает мне 100 строк, как я понимаю, потому что каждая строка в ClothingObservation соответствует каждой строке в HygieneObservation. Я думал, что использование GROUP BY приведет к удалению дубликатов, но я явно делаю что-то не так. Любые подсказки?

Изменить: вот мои данные прямо сейчас (детали опущены).

Report:

ReportId    Period     Reporter DepartmentId
----------- ---------- -------- ------------
1           2016-05-01 username 1

ClothingObservation:

ClothingObservationId ... ReportId
--------------------- ... -----------
1                     ... 1
2                     ... 1
3                     ... 1
4                     ... 1
5                     ... 1
6                     ... 1
7                     ... 1
8                     ... 1
9                     ... 1
10                    ... 1

HygieneObservation:

HygieneObservationId ... ReportId
-------------------- ... -----------
3                    ... 1
4                    ... 1
5                    ... 1
6                    ... 1
7                    ... 1
8                    ... 1
9                    ... 1
10                   ... 1
12                   ... 1
13                   ... 1

Редактировать 2: если я запускаю эти два запроса, я получаю желаемый результат (опять же, нерелевантные детали опущены из результата):

SELECT * FROM Report
    LEFT JOIN ClothingObservation ON
    (ClothingObservation.ReportId = Report.ReportId)
SELECT * FROM Report
    LEFT JOIN HygieneObservation ON
    (HygieneObservation.ReportId = Report.ReportId)

ReportId    Period     Reporter DepartmentId ClothingObservationId ...  ReportId
----------- ---------- -------- ------------ --------------------- ...- -----------
1           2016-05-01 username 1            1                     ...  1
1           2016-05-01 username 1            2                     ...  1
1           2016-05-01 username 1            3                     ...  1
1           2016-05-01 username 1            4                     ...  1
1           2016-05-01 username 1            5                     ...  1
1           2016-05-01 username 1            6                     ...  1
1           2016-05-01 username 1            7                     ...  1
1           2016-05-01 username 1            8                     ...  1
1           2016-05-01 username 1            9                     ...  1
1           2016-05-01 username 1            10                    ...  1

ReportId    Period     Reporter DepartmentId HygieneObservationId ... ReportId
----------- ---------- -------- ------------ -------------------- ... -----------
1           2016-05-01 username 1            3                    ... 1
1           2016-05-01 username 1            4                    ... 1
1           2016-05-01 username 1            5                    ... 1
1           2016-05-01 username 1            6                    ... 1
1           2016-05-01 username 1            7                    ... 1
1           2016-05-01 username 1            8                    ... 1
1           2016-05-01 username 1            9                    ... 1
1           2016-05-01 username 1            10                   ... 1
1           2016-05-01 username 1            12                   ... 1
1           2016-05-01 username 1            13                   ... 1

Моя цель — получить этот вывод (или что-то подобное) с помощью одного запроса.


sql sql-server tsql sql-server-2014
person Tobbe    schedule 13.06.2016    source источник
comment
Ну, вы должны определить, что делать с несколькими совпадениями. Если на один отчет ссылаются со многими наблюдениями, какое наблюдение следует отображать? Или даже количество наблюдений.   -  person Ivan Starostin    schedule 13.06.2016
comment
Поскольку вы выбираете и ClothingObservationId, и HygieneObservationId, существует 10*10 возможных комбинаций. Поэтому дубликатов нет.   -  person Robert Kock    schedule 13.06.2016
comment
@RobertKock, это правда: D, возможно, мне придется перефразировать ...   -  person Tobbe    schedule 13.06.2016
comment
@IvanStarostin На каждый отчет ссылается всего 20 наблюдений. Поэтому мне нужно не более 20 строк со значениями NULL, где нет данных для отображения. Если это имеет смысл?   -  person Tobbe    schedule 13.06.2016
comment
@Tobbe добавьте к вопросу необходимые выходные данные для вашего отчета.   -  person Ivan Starostin    schedule 13.06.2016
comment
@IvanStarostin Готово! Надеюсь, это принесет некоторые разъяснения. Теперь я получаю все наблюдения, ссылающиеся на отчет.   -  person Tobbe    schedule 13.06.2016

Ответы (2)


arrow_upward
2
arrow_downward

Происходит следующее: объединение Report (1 строка) с ClothingObservation (10 строк) дает 10 строк (1 x 10), затем вы присоединяетесь к HygieneObservation (10 строк), что дает вам 100. Причина, по которой это происходит, заключается в том, что после начального join у вас есть 10 строк с одним и тем же ReportID, поэтому следующее соединение берет каждую из этих 10 строк и объединяется с 10 строками в HygieneObservation.

Решение для «20 строк со значениями NULL»:

SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 
    ClothingObservation.ClothingObservationId,
     NULL AS HygieneObservationId
FROM Report
LEFT JOIN ClothingObservation ON
    (ClothingObservation.ReportId = Report.ReportId)
UNION ALL
SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 
    NULL AS ClothingObservationId,
    HygieneObservation.HygieneObservationId
FROM Report
LEFT JOIN HygieneObservation ON
    (HygieneObservation.ReportId = Report.ReportId)

Как это работает.
По сути, вы пишете два отдельных запроса: один соединяет Report и ClothingObservation, а другой соединяет Report с HygieneObservation. Затем вы объединяете два запроса с помощью UNION ALL.

Решение для "получить 10 строк"

Это сложно, поскольку включает в себя то, что я называю «вертикальным слиянием» или «соединением слиянием». Ниже приведен запрос (Обновление: я его протестировал).

SELECT 
    Report.ReportId, 
    Report.Period, 
    Report.Reporter, 
    Report.DepartmentId, 

    MergedObservations.ClothingObservationId,
    MergedObservations.HygieneObservationId
FROM Report
    LEFT JOIN 
            ( SELECT COALESCE( ClothingObservation.ReportID, HygieneObservation.ReportID ) AS ReportID,
                    HygieneObservationID, ClothingObservationID -- Add appropriate columns
            FROM
                        ( SELECT ROW_NUMBER() OVER( PARTITION BY ReportID ORDER BY ClothingObservationID ) AS ResultID, ReportID, ClothingObservationID
                        FROM ClothingObservation ) AS ClothingObservation
               FULL OUTER JOIN
                        ( SELECT ROW_NUMBER() OVER( PARTITION BY ReportID ORDER BY HygieneObservationID  ) AS ResultID, ReportID, HygieneObservationID
                        FROM HygieneObservation ) AS HygieneObservation
                    ON ClothingObservation.ReportID = HygieneObservation.ReportID
                        AND ClothingObservation.ResultID = HygieneObservation.ResultID
            ) AS MergedObservations
        ON Report.ReportID = MergedObservations.ReportID

Как это работает:
Поскольку ClothingObservation и HygieneObservationId не связаны напрямую друг с другом и имеют разное количество строк для каждого ReportID, я использую функцию ROW_NUMBER() для создания ключа соединения. Затем я выполняю «объединение слиянием», используя ReportID и вывод функции ROW_NUMBER().

Образец данных

Я преобразовал ваши образцы данных в пригодные для использования данные таблицы для проверки вышеуказанных запросов.

CREATE TABLE Report( ReportId INT, Period DATETIME, Reporter VARCHAR( 20 ), DepartmentId INT )
CREATE TABLE ClothingObservation( ClothingObservationID INT, ReportId INT )
CREATE TABLE HygieneObservation( HygieneObservationID INT, ReportId INT )

INSERT INTO Report
VALUES( 1, '2016-05-01', 'username', 1 )

INSERT INTO ClothingObservation
VALUES
( 1, 1 ), ( 2, 1 ), ( 3, 1 ), ( 4, 1 ), ( 5, 1 ), ( 6, 1 ), ( 7, 1 ), ( 8, 1 ), ( 9, 1 ), ( 10, 1 )

INSERT INTO HygieneObservation
VALUES
( 3, 1 ), ( 4, 1 ), ( 5, 1 ), ( 6, 1 ), ( 7, 1 ), ( 8, 1 ), ( 9, 1 ), ( 10, 1 ), ( 11, 1 ), ( 12, 1 ), ( 13, 1 )
person Alex    schedule 13.06.2016
comment
Спасибо за Ваш ответ! Я думаю, то, что я пытался сделать, было сложнее, чем я думал :) Возможно, я переосмыслю, как спроектировать базу данных, чтобы упростить этот тип запроса. Еще раз большое спасибо. - person Tobbe; 13.06.2016
comment
Поскольку я тестировал приведенные выше запросы, используя ваши образцы данных, вы опубликовали ответ. Во всяком случае, я исправил второй запрос и включил тестовые данные для потомков. - person Alex; 13.06.2016

arrow_upward
2
arrow_downward

Вы также можете попробовать использовать следующий запрос:

SELECT
    ReportId = ISNULL(v1.ReportId, v2.ReportId),
    Period = ISNULL(v1.Period, v2.Period), 
    Reporter = ISNULL(v1.Reporter, v2.Reporter), 
    DepartmentId = ISNULL(v1.DepartmentId, v2.DepartmentId),
    v1.ClothingObservationId, 
    v2.HygieneObservationId
FROM
(
    SELECT 
        RowNumber = ROW_NUMBER() OVER(Partition BY r.ReportId ORDER BY c.ClothingObservationId),
        r.ReportId, 
        r.Period, 
        r.Reporter, 
        r.DepartmentId, 
        c.ClothingObservationId
    FROM 
        Report r
        LEFT JOIN ClothingObservation c ON c.ReportId = r.ReportId) v1
FULL JOIN 
(
    SELECT 
        RowNumber = ROW_NUMBER() OVER(Partition BY r.ReportId ORDER BY h.HygieneObservationId),
        r.ReportId, 
        r.Period, 
        r.Reporter, 
        r.DepartmentId, 
        h.HygieneObservationId
    FROM Report r
    LEFT JOIN HygieneObservation h ON h.ReportId = r.ReportId) v2 ON v1.RowNumber = v2.RowNumber AND v1.ReportId = v2.ReportId
ORDER BY ReportId
person dee.ronin    schedule 13.06.2016
comment
Строго говоря, ваш запрос неверен, но он, вероятно, возвращает правильные результаты на основе выборочных данных только с одной записью в таблице отчетов. Ваше объединение должно включать ReportID, чтобы обеспечить соответствие полей таблицы отчетов в объединении. Кроме того, оконная функция должна быть разделена по ReportID, чтобы это соединение было успешным. - person Alex; 13.06.2016
comment
Вы правы, я добавил записи в таблицы, и это не удалось. Я уже обновил свой ответ. - person dee.ronin; 13.06.2016