环境:MS SQL Server 2016.
我有一个表格,其中包含(Jasper 报告(这样的布局表示形式(为简洁起见,仅显示相关字段(:
ID Name Key Version
1 CoverLetter <guid1> 1.00.00
2 Contract <guid2> 1.00.00
3 CoverLetter <guid1> 1.00.01
目标:
我需要一个额外的计算字段,该字段根据 记录是否是任何给定布局的最高版本(相同的布局,但不同的版本具有相同的键,不同的布局具有不同的键(。
喜欢这个:
ID: Name: Key: Version: isHighestVersion: (calculated field)
1 CoverLetter <guid1> 1.00.00 false
2 Contract <guid2> 1.00.00 true
3 CoverLetter <guid1> 1.00.01 true
仅显示每个布局的最高版本的 SQL 查询如下所示:
( SELECT TACMasterlayouts.*
FROM
(SELECT
TACMasterLayoutKey, MAX(TACMasterLayoutVersion) as TACMasterLayoutVersion
FROM
TACMasterlayouts
GROUP BY
TACMasterLayoutKey) AS latest_TACMasterLayouts
INNER JOIN
TACMasterlayouts
ON
TACMasterlayouts.TACMasterLayoutKey = latest_TACMasterLayouts.TACMasterLayoutKey AND
TACMasterlayouts.TACMasterLayoutVersion = latest_TACMasterLayouts.TACMasterLayoutVersion
)
但是我需要所有记录 - 每个相同键具有最高版本号的记录标记为 true,其余记录标记为 false。
我已经做了什么:搜索了谷歌和SO,但没有找到任何类似的东西,我可以转化为我需要的东西。
只需将内部连接更改为左外部连接
并在您的 选择 EG
CASE WHEN latest_TACMasterLayouts.TACMasterLayoutKey IS NOT NULL THEN 1 ELSE 0 END as isHighestVersion
谢谢约翰, 这为我指明了正确的方向。
它必须是右外连接 - 否则仅显示具有最高版本的记录。
作为此处的参考,完整的工作代码:
SELECT TACMasterlayouts.*, CASE WHEN latest_TACMasterLayouts.TACMasterLayoutKey IS NOT NULL THEN 1 ELSE 0 END as isHighestVersion
FROM
(SELECT TACMasterLayoutKey, MAX(TACMasterLayoutVersion) as TACMasterLayoutVersion
FROM
TACMasterlayouts
GROUP BY
TACMasterLayoutKey) AS latest_TACMasterLayouts
RIGHT OUTER JOIN
TACMasterlayouts
ON
TACMasterlayouts.TACMasterLayoutKey = latest_TACMasterLayouts.TACMasterLayoutKey AND
TACMasterlayouts.TACMasterLayoutVersion = latest_TACMasterLayouts.TACMasterLayoutVersion
)
您需要进行一些解析才能获得所需的结果。
首先,您将版本号拆分为单独的整数,然后根据它们分配row_number
,然后根据行号,您将 1-true 或 0-false 放在一个额外的列中,我称之为IsLatest
.
在 SQL Server 中没有true
或false
,您可以使用BIT
数据类型,它有两个值(就像布尔值一样(:1 和 0。
请尝试以下查询:
declare @tbl table(ID int,Name varchar(20),[Key] varchar(10),Version varchar(10));
insert into @tbl values
(1,'CoverLetter','<guid1>','1.00.00'),
(2,'Contract','<guid2>','1.00.00'),
(3,'CoverLetter','<guid1>','1.00.01');
select ID, [Key], [version],
case when rn = 1 then 1 else 0 end IsLatest
from (
select *,
row_number() over (order by
cast(substring([version], 1, FirstDot - 1) as int) desc,
cast(substring([version], FirstDot + 1, SecondDot - FirstDot - 1) as int) desc,
cast(substring([version], SecondDot + 1, 100) as int) desc) rn
from (
select ID, [Key], [version],
charindex('.', [version]) FirstDot,
charindex('.', [version], charindex('.', [version]) + 1) SecondDot
from @tbl
) a
) a