更好的方式来表示查询 [英] Better way to represent the query
问题描述
嗨
我想以更好的方式代表以下查询,请你帮忙
I am trying to represent the below query in a better way , can you please help
从[dbo]中选择*。[表1] A与(nolock)
其中A. [value] 不为空且[ENDDate] =(从[dbo]中选择max(enddate)。[Table1] B with(nolock)其中A.id = B.id)
select * from [dbo].[Table1] A with (nolock)
where A.[value] is not null and [ENDDate]=(select max(enddate) from [dbo].[Table1] B with (nolock) where A.id=B.id)
推荐答案
一个好的开始是取出NOLOCK。 : - )
A good start is to take out the NOLOCK. :-)
;带编号AS(
SELECT col1,col2,col3,...,
$
row_number()OVER(按ID分类截止日期DESC)rowno
FROM Table1
)
SELECT col1,col2,col3, ... ...
FROM 编号WHERE rowno = 1
; WITH numbering AS (
SELECT col1, col2, col3, ...,
row_number() OVER(PARTITION BY id ORDER BY enddate DESC) rowno
FROM Table1
)
SELECT col1, col2, col3, ...
FROM numbering WHERE rowno = 1
这篇关于更好的方式来表示查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!