SQL如何从1-多选择日期
在SQL 2005存储的proc中，我需要运行一个包含1-M的查询。我只需要返回多张表中的一张，也就是最早日期的。
我看过In SQL how do I write a query to return 1 record from a 1 to many relationship?
和SQL conundrum, how to select latest date for part, but only 1 row per part (unique)
但是，我不确定在我的情况下什么是最好的解决方案，因为我还在对临时表进行插入，并使用动态排序和分页。
这是我的SQL。我想要的是返回许多行Foo，但只返回我传入的起始数据参数和结束数据参数之间最早的b.CreatedDate，在这里，每个Foo通常有大约5行。
DECLARE @StartDate datetime
DECLARE @EndDate datetime
INSERT INTO @Results
SELECT distinct
f.Name,
f.Price
b.CreatedDate ,
// loads more columns removed for brevity
FROM
foo f
join bar b on f.Id = b.fooId
// loads more table removed for brevity
WHERE
(@x is null OR f.Id = @x)
AND (@Deal is null OR f.IsDeal = @Deal)
AND (@StartDate is null OR sd.SailingDate >= @StartDate)
AND (@EndDate is null OR sd.SailingDate <= @EndDate)
// loads more filters removed for brevity
declare @firstResult int, @lastResult int
set @firstResult = ((@PageNumber-1) * @ItemsPerPage) + 1;
set @lastResult = @firstResult + @ItemsPerPage;
select @TotalResults = count(1) from @Results;
WITH ResultItems AS
(
SELECT *, ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @SortBy = 'priceLow' THEN Price END ASC,
CASE WHEN @SortBy = 'Soonest' THEN CreatedDate END ASC,
CASE WHEN @SortBy = 'priceHigh' THEN Price END DESC
) As RowNumber
FROM @Results r
)
SELECT * from ResultItems
WHERE RowNumber >= @firstResult AND RowNumber < @lastResult
ORDER BY
CASE
WHEN @SortBy = 'priceHigh' THEN (RANK() OVER (ORDER BY Price desc))
WHEN @SortBy = 'priceLow' THEN (RANK() OVER (ORDER BY Price))
WHEN @SortBy = 'Soonest' THEN (RANK() OVER (ORDER BY CreatedDate ))
END 这个查询将返回多个“b.CreatedDate”，而不是我的筛选器之间最早的一个。
更新，因此我想看看我的源数据是否是：
Foo
___
1 , Hello
2 , There
Boo
___
1, 1, 2011-2-4
2, 1, 2011-3-6
3, 1, 2012-12-21
4, 2, 2012-11-2结果将是
1, Hello,2011-2-4
2, There, 2012-11-2回答 1
Stack Overflow用户
发布于 2011-05-11 13:06:32
我想我刚在查询的顶部添加了一个CTE。
;with cteMinDate as (
select FooId, min(CreatedDate) As CreatedDate
from Bar WHERE
(@StartDate is null OR CreatedDate>= @StartDate)
AND (@EndDate is null OR CreatedDate<= @EndDate)
group by FooId
)和这里显示的一样，SQL conundrum, how to select latest date for part, but only 1 row per part (unique)。通过这样做，我可以从我的主查询中删除日期查询部分，并且只在CTE中执行一次。
https://stackoverflow.com/questions/5964412
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号