Início / dba / Perguntas / 77266
Accepted
lumo
Asked: 2014-09-23 04:51:46 +0800 CST

Alternativas para uma consulta LEFT OUTER JOIN

  • 772

Problema

Eu tenho uma instrução SQL com um LEFT OUTER JOINque funciona bem em nosso arquivo Microsoft SQL Server. Meu problema é que tenho que ser compatível H2 Databasee este tem um bug com OUTER JOINS.

Consulta SQL

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

Mesa

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);

dados de exemplo

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');
sql-server join

3 respostas

  1. Você pode reescrever o LEFT JOINpara uma subconsulta, mas ainda pode compilar como uma consulta de junção externa - não tenho conhecimento desses aspectos da plataforma H2.

    SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');

    Ou você pode criar algum tipo de tabela temporária (ou uma tabela regular se o H2 não suportar tabelas temporárias) e preenchê-la em três etapas. Novamente, isso provavelmente ainda será compilado usando um anti-semijoin ou algum outro tipo de junção externa, mas talvez funcione como uma solução alternativa para o seu bug:

    --- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;

    Mas essas não são soluções bonitas.

    • 3
  2. Best Answer

    Solução

    isso não é exatamente o que eu pedi, mas resolve o problema da consulta original falhar, H2 Database obrigado ypercube, sua sugestão está correta

    SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END AS nlsGuid
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

    resulta em um resultado correto.

    mas devido ao (muito) melhor desempenho eu processo toda a tabela para objetos em meu aplicativo agora e pego os objetos (selecione qual é o correto por código)

    • 1

  3. eu tive duas consultas sem junção externa, que funcionaram rápido e corretas, Microsoft SQL Servermas ambas não funcionaram em H2 Database uma delas porque MSSQLusa + para concatenar strings e H2usa CONCAT (que é uma porcaria porque afaik isso não corresponde às especificações SQL)

    minha solução final é criar uma única consulta em meu código e encontrar o material correto com o código.

    Uma das consultas que tentei foi esta (funciona, Microsoft SQL Servermas não em H2 devido à sintaxe concatenada diferente)

    SELECT * FROM tSysNLS
WHERE nlsGuid = CASE
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?2) then ?4+'.'+?5+'.'+?2
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?1) then ?4+'.'+?5+'.'+?1
ELSE ?4+'.'+?5+'.'+?3
END

    onde ?# são os parâmetros
    ?1 = 'de_AT'
    ?2 = 'de'
    ?3 = 'en'
    ?4 = 'Contato'
    ?5 = 'primeiroNome'

    • -4

relate perguntas