我下面有一个脚本,它显示来自不同链接服务器下的不同数据库的结果。该查询只是通过 union all 连接或链接,其结果似乎没有问题。我唯一的问题是我想通过使用读取率较高的结果按降序显示结果,无论它来自哪个链接服务器。我知道是否可以使用 ff 脚本转换为子查询,但我不知道如何将它与 join 一起使用。请参阅下面的脚本:
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
结果示例: