Início / dba / Perguntas / 157402
Accepted
Jon49
Asked: 2016-12-07 12:04:20 +0800 CST

JOIN 3 Tables sem Union ALL nem consultas aninhadas com vários IDs

  • 772

Não tenho certeza se isso é possível, mas gostaria de juntar várias tabelas com vários IDs sem UNION ALLou consultas aninhadas. Você pode ver a pergunta relacionada aqui , que mostra tudo na exibição de tabela. Eu gostaria de criar um table view, mas não tenho permissão para usar o que foi mencionado acima. Eu já descobri como fazer isso comUNION ALL .

Então, eu tenho a Maintabela que faz referência a uma tabela chamada Fact. A Facttabela contém dados que podem ser referenciados por LanguageID, ClientIDou StatusID(revisão). As tabelas Maine Factsão relacionadas por uma tabela intermediária chamada FactLink(não mostrada para simplificar).

A tabela indexável de resultado final MainViewque eu gostaria de obter se parece com:

Vista principal

MainID | StatusOrder | LanguageID | ClientID | Description   | Disclaimer  | Other
-------+-------------+------------+----------+---------------+-------------+------
50     | 10          | 1          | 1        | Some text     | Disclaimer1 | Blah
50     | 10          | 2          | 1        | Otro texto    | NULL        | Blah
50     | 20          | 1          | 2        | Modified text | NULL        | Blah
55     | 10          | 1          | 1        | Some text 2   | Disclaimer2 | Blah Blah
55     | 10          | 1          | 2        | NULL          | Disclaimer3 | Blah Blah

DROP TABLE IF EXISTS #main
CREATE TABLE #main
(
    ID INT NOT NULL,
    DescriptionID INT NOT NULL,
    DisclaimerID INT NOT NULL,
    Other NVARCHAR(500)
)

Drop table if exists #fact
CREATE TABLE #fact
(
    FactID INT NOT NULL,
    LanguageID INT NOT NULL,
    StatusID INT NOT NULL,
    ClientID INT NOT NULL,
    Description NVARCHAR(MAX)
)

DROP TABLE IF EXISTS #status
CREATE TABLE #status
(
    StatusID INT NOT NULL,
    [Order] INT NOT NULL
)

INSERT INTO #main
    (ID, DescriptionID, DisclaimerID, Other)
    VALUES
    (50, 1, 2, 'Blah'),
    (55, 4, 3, 'Blah Blah')

INSERT INTO #fact
    (FactID, LanguageID, StatusID, ClientID, Description)
    VALUES
    (1, 1, 1, 1, N'Some text'),
    (1, 2, 1, 1, N'Otro texto'),
    (1, 1, 3, 2, N'Modified text'),
    (2, 1, 1, 1, N'Disclaimer1'),
    (3, 1, 1, 1, N'Disclaimer2'),
    (3, 1, 2, 1, N'Disclaimer3'),
    (4, 1, 1, 1, N'Some text 2')

INSERT INTO #status
    (StatusID, [Order])
    VALUES
    (1, 10),
    (2, 100),
    (3, 20)

Aqui está o que eu tenho até agora. Mas não inclui a última entrada e não foi responsável pelo arquivo StatusOrder. Não tenho certeza para onde ir a partir daqui.

SELECT
    t.ID, t.Other,
    fDescription.Description,
    fDisclaimer.Description Disclaimer,
    COALESCE(fDescription.LanguageID, fDisclaimer.LanguageID) LanguageID,
    COALESCE(fDescription.ClientID, fDisclaimer.ClientID) ClientID,
    COALESCE(fDescription.StatusID, fDisclaimer.StatusID) StatusID
FROM #main t
JOIN #fact fDescription
    ON fDescription.FactID = t.DescriptionID
LEFT OUTER JOIN #fact fDisclaimer
    ON fDisclaimer.FactID = t.DisclaimerID
    AND fDisclaimer.ClientID = fDescription.ClientID
    AND fDisclaimer.LanguageID = fDescription.LanguageID
    AND fDisclaimer.StatusID = fDescription.StatusID
sql-server join

1 respostas

  1. Best Answer

    Responderei à pergunta conforme solicitado, mas espero que isso não resolva seu problema real. Você deseja traduzir a consulta que você tem na outra postagem em uma consulta que pode ser transformada em uma exibição indexada. O maior problema que vejo é que seu modelo de dados exige que os dados de várias linhas da tabela "fato" sejam combinados em uma única linha. Tal requisito luta contra a funcionalidade de visualização indexada. Muitas das restrições do T-SQL quase parecem projetadas para interromper esse tipo de operação (junto com outras):

    restrições de exibição de índice

    As entradas mais importantes nessa lista para o seu problema são as proibições de MAX, MIN, autojunções, subconsultas, APPLY e UNPIVOT. Todos eles podem ser usados ​​para reescrever sua consulta UNION ALL, mas nenhum deles é permitido em exibições indexadas. Existe uma maneira de reescrevê-lo, mas você ficará preso mais adiante. Você começou com a seguinte consulta:

    SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  f.Description,
  NULL Disclaimer,
  Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  NULL Description,
  f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID;

    Há pelo menos uma maneira de reescrever essa consulta de maneira que seja uma definição de exibição indexada válida. Primeiro crie uma tabela fictícia com apenas duas linhas:

    CREATE TABLE DIM_NUMBERS (NUM INT NOT NULL);
INSERT INTO DIM_NUMBERS VALUES (1), (2);

    Você pode então definir sua visão assim:

    CREATE VIEW dbo.X_VIEW
WITH SCHEMABINDING  
AS  
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  CAST(CASE WHEN d.NUM = 1 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) [Description],
  CAST(CASE WHEN d.NUM = 2 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) Disclaimer,
  Main.Other
FROM dbo.Main
CROSS JOIN dbo.DIM_NUMBERS d
JOIN dbo.Fact f
  ON (f.FactID = Main.DescriptionID AND d.NUM = 1) OR (f.FactID = Main.DisclaimerID AND d.NUM = 2)
JOIN dbo.Status s ON s.StatusID = f.StatusID;

CREATE UNIQUE CLUSTERED INDEX IDX_X_VIEW   
    ON dbo.X_VIEW  (ID, [Order], LanguageID, ClientID, [Description], Disclaimer, Other);

    Reescrevi sua consulta para não usar UNION ALL, mas não incluí o GROUP BY porque MIN e MAX não são permitidos em exibições indexadas. Não há nenhuma função agregada que irá ajudá-lo aqui porque você tem uma coluna NVARCHAR. Se você tivesse uma coluna numérica, provavelmente poderia fazer isso com uma coluna SUM(COALESCE(COLUMN_NAME, 0)), mas não pode aplicar a agregação SUM a NVARCHAR.

    Em vez de tentar encaixar uma consulta em uma definição de exibição indexada, tentaria pensar em outras soluções. Você poderia simplesmente armazenar os resultados em uma tabela? Dependendo do comprimento da coluna de descrição, a diferença de espaço entre ela e uma exibição indexada pode não ser tão grande. Você pode alterar o modelo de dados de alguma forma que ajude?

    • 3

relate perguntas