如何在没有WHERE子句的情况下编写这个T_SQL联接？

Question

我不得不尝试编写一个JOIN，它将产生一个结果集，如下面的输出示例所示，但是没有 WHERE子句，我只是为了显示正确的数据应该是什么样子而包含了这个子句。如果排除WHERE子句，则会给出错误的结果。

#TblA表列出了假设站点上的Server实例，其中包含Server版本和内部版本号，例如。'12.0.5579.0‘

#TblB表是所有SQL Server版本和生成编号的列表(本例减少了此列表)。

其目的是获得一个结果集，其中列出了每个Server实例、SQLVersion和ProductVersion (Build)以及在#TblB中可用的任何ProductVersion (等同于Service和累积更新)，后者比给定SQL实例的版本和ProductVersion的当前ProductVersion要高。

如果查看示例结果集，它应该相对于#TblB中较高的ProductVersion行重复#TblA行。顺便说一下，这将允许Reporting中的分组报告。

SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#tblA') IS NOT NULL
    DROP TABLE #tblA 

create table #tblA -- build info for specific servers
(
    SQLInstance varchar(128),
    SQLVersion varchar(32),
    ProductVersion varchar(32)
)
GO
INSERT INTO #tblA VALUES ( 'MyServer1', 'SQL Server 2016', '13.0.4001.0' )
INSERT INTO #tblA VALUES ( 'MyServer2', 'SQL Server 2016', '13.0.4001.0' )
INSERT INTO #tblA VALUES ( 'MyServer3', 'SQL Server 2014', '12.0.2000.8' )
INSERT INTO #tblA VALUES ( 'MyServer4', 'SQL Server 2014', '12.0.5000.0' )
INSERT INTO #tblA VALUES ( 'MyServer5', 'SQL Server 2012', '11.0.6020.0' )
INSERT INTO #tblA VALUES ( 'MyServer6', 'SQL Server 2012', '11.0.5058.0' )
GO

IF OBJECT_ID('tempdb..#tblB') IS NOT NULL
    DROP TABLE #tblB 

create table #tblB -- ALL builds available
(
    SQLVersion varchar(32),
    ProductVersion varchar(32)
)

INSERT INTO #tblB VALUES ( 'SQL Server 2017', '14.0.3025.34' )
INSERT INTO #tblB VALUES ( 'SQL Server 2017', '14.0.3023.8' )
INSERT INTO #tblB VALUES ( 'SQL Server 2017', '14.0.3022.28' )
INSERT INTO #tblB VALUES ( 'SQL Server 2017', '14.0.3015.40' )
INSERT INTO #tblB VALUES ( 'SQL Server 2017', '14.0.3008.27' )
INSERT INTO #tblB VALUES ( 'SQL Server 2017', '14.0.3006.16' )

INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.5026.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4474.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4466.4' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4457.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4451.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4446.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4435.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4422.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4411.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2016', '13.0.4001.0' )

INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5579.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5571.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5557.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5556.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5553.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5546.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5540.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5538.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5522.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5511.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.5000.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.4522.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.4511.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2014', '12.0.2000.8' )

INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.7001.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6607.3' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6598.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6594.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6579.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6567.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6544.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6540.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6537.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6523.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6518.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.6020.0' )
INSERT INTO #tblB VALUES ( 'SQL Server 2012', '11.0.5678.0' )

查询内容如下：

-- The following gives a correct dataset in the format required for grouped reporting but ONLY because of the WHERE clause
-- Want a neat query that will produce the same format for all versions without the WHERE clause 
SELECT
    a.*,
    b.*
FROM
     #tblB b
     left join  #tblA a
     ON  b.ProductVersion > a.ProductVersion 
     AND a.SQLVersion = b.SQLVersion
WHERE
    a.SQLVersion = 'SQL Server 2014'

产出：-

**SQLInstance   SQLVersion  ProductVersion  SQLVersion  ProductVersion**
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5579.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5571.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5557.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5556.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5553.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5546.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5540.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5538.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5522.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5511.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.5000.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.4522.0
MyServer3   SQL Server 2014 12.0.2000.8 SQL Server 2014 12.0.4511.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5579.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5571.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5557.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5556.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5553.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5546.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5540.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5538.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5522.0
MyServer4   SQL Server 2014 12.0.5000.0 SQL Server 2014 12.0.5511.0

Answer 1

这是你想要的吗？

SELECT a.*, b.*
FROM #tblB b JOIN
      #tblA a
      ON b.ProductVersion > a.ProductVersion AND
         a.SQLVersion = b.SQLVersion
ORDER BY a.SQLVersion;

我不明白您为什么要在最后的查询中使用LEFT JOIN。由于使用了INNER JOIN子句，您的版本实际上是一个WHERE。