Início / dba / Perguntas / 338142
Accepted
Caspersky12
Asked: 2024-03-28 16:25:23 +0800 CST

Exibir em ordem asc de vários bancos de dados em vários servidores vinculados e adicionar dois parâmetros de data para que eu possa integrá-los ao meu aplicativo

  • 772

Eu tenho um script abaixo onde ele exibe um resultado de diferentes bancos de dados em diferentes servidores vinculados. A consulta está apenas conectada ou vinculada através da união de todos e parece que não há problema no resultado. Meu único problema é que quero exibir o resultado em ordem decrescente usando o resultado mais alto da taxa de leitura, independentemente do servidor vinculado de onde ele veio. Tenho uma ideia se é possível usar o script ff para converter em subconsulta, mas não sei como posso usá-lo com join também. Veja o roteiro abaixo:

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER1].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER1].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

UNION ALL

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER2].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER2].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

UNION ALL

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER3].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER3].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

UNION ALL

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER4].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER4].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

exemplo RESULTADO:

insira a descrição da imagem aqui

sql-server

1 respostas

  1. Best Answer

    Como Dan apontou nos comentários, quando você adiciona uma ORDER BYcláusula à última consulta em uma cláusula UNION ALL(or UNION), o mecanismo é inteligente o suficiente para saber que você deseja que todos os resultados dessa UNION ALLcláusula sejam ordenados dessa forma.

    Portanto, para atingir seu objetivo de ordenar os resultados por ReadRatecoluna, tudo que você precisa é de uma cláusula final ORDER BYcomo esta:

    SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER1].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER1].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

UNION ALL

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER2].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER2].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

UNION ALL

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER3].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER3].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

UNION ALL

SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
FROM [LINKED-SERVER4].[Database1].DBO.Table1 t1 
INNER JOIN
[LINKED-SERVER4].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
ORDER BY ReadRate DESC

    Observe como a ORDER BYcláusula faz referência a um alias em vez de uma coluna, mostrando que isso significa todo o conjunto de resultados, não apenas aquela consulta específica.

    Se você quiser fazer uma codificação extra para se sentir caloroso e confuso sobre isso (também conhecido como torná-lo mais legível para futuros desenvolvedores, talvez), você pode simplesmente colocar sua consulta entre parênteses para torná-la uma subconsulta e, em seguida, aplicar a ORDER BYcláusula ao lado de fora da subconsulta assim:

    SELECT TrxnDTime, PlazaCode, PlazaName, Lane, Zone, TotalRead, TotalManual, TotalCount, ReadRate
FROM
(
    SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
    FROM [LINKED-SERVER1].[Database1].DBO.Table1 t1 
    INNER JOIN
    [LINKED-SERVER1].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
    WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
    GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

    UNION ALL

    SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
    FROM [LINKED-SERVER2].[Database1].DBO.Table1 t1 
    INNER JOIN
    [LINKED-SERVER2].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
    WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
    GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

    UNION ALL

    SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
    FROM [LINKED-SERVER3].[Database1].DBO.Table1 t1 
    INNER JOIN
    [LINKED-SERVER3].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
    WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
    GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName

    UNION ALL

    SELECT        TRXN_DATE AS TrxnDTime, CONVERT(VARCHAR(5), Plaza) PlazaCode, t2.PlazaName, Lane, t2.Zone, SUM(TOTAL_READ) TotalRead, SUM(TOTAL_MANUAL) TotalManual, SUM(TOTAL_MANUAL) + SUM(TOTAL_READ) TotalCount, CONVERT(DECIMAL(18, 2), CONVERT(DECIMAL(18, 2), (SUM(TOTAL_READ))) / CONVERT(DECIMAL(18, 2), (SUM(TOTAL_MANUAL) + SUM(TOTAL_READ))) * 100) AS ReadRate 
    FROM [LINKED-SERVER4].[Database1].DBO.Table1 t1 
    INNER JOIN
    [LINKED-SERVER4].[Database1].DBO.Table2 t2 ON t1.Plaza = t2.PlazaCode
    WHERE CONVERT(DATE, TRXN_DATE) = CONVERT(DATE, GETDATE()) AND t1.Plaza > 0
    GROUP BY TRXN_DATE, Plaza, Lane, t2.Zone, t2.PlazaName
) AS Results
ORDER BY ReadRate DESC
    • 2

relate perguntas