SQL查询或SP问题 [英] SQL Query or SP problem

问题描述

大家好，

i面临查询构建的问题

1： - 我有2列（a）displayorder（b）posteddate（c）ImageName

当特定日期到来时，用户可以上传展示订单的许多照片，特定日期将显示该横幅广告EG

Hello all,
i am facing a problem to built by query
1:-I have 2 columns (a)displayorder (b)posteddate (c)ImageName
User can upload many pics for a displayorder with a posteddate when the particular date will come that banner should display EG

DispalyeOrder     PostedDate         ImageName
-------------     -----------        -------
1                     9/1/2013       Img1
2                     9/1/2013       Img2  
2                     10/1/2013      Img2New  
3                     9/1/2013       Img3 

今天 img1 => img2 => Img3将显示

明天 img1 => Img2New => Img3将显示



请帮帮我。

谢谢

Raj

Today img1=>img2=>Img3 will display
Tomorrow img1=>Img2New=>Img3 Will display

Please help me.
Thanks
Raj

推荐答案

CREATE TABLE #Test
(
	DisplayOrder int,
	PostedDate date,
	ImageName nvarchar(50)
)

declare @DateToSelect datetime

set @DateToSelect = '9/1/2013'

--Uncomment this line to see it working for the 10th.
--set @DateToSelect = '10/1/2013'

insert into #Test values (1, '9/1/2013', 'Img1')
insert into #Test values (2, '9/1/2013', 'Img1')
insert into #Test values (2, '10/1/2013', 'Img1New')
insert into #Test values (3, '9/1/2013', 'Img1')

select 
	#Test.DisplayOrder, 
	#Test.PostedDate, 
	#Test.ImageName 
from 
	#Test
join
(
	select
	displayOrder, 
	Max(PostedDate) as 'PostedDate'
	from
	#Test
	where PostedDate <= @DateToSelect
	group by displayOrder
) as SQ on (SQ.DisplayOrder = #Test.DisplayOrder) AND (SQ.PostedDate = #Test.PostedDate)


DROP TABLE #Test

SELECT  Displayorder
       ,max(PostedDate) as PostedDate
       ,ImageName
FROM    MyTable
WHERE   PostedDate < SYSDATE
GROUP BY DisplayOrder
        ,ImageName

SYSDATE是特定于Oracle的，将其交换到适合您数据库的任何内容。

忽略我的解决方案，这是不正确的，使用Francois Viljoens或Aarti Meswanias相反。

SYSDATE is Oracle specific, exchange it to whatever is appropriate in your database.
Ignore my solution, it''s incorrect, use Francois Viljoens or Aarti Meswanias instead.

这种方式...

This way...

select * from
(
select row_Number() over(partition by DispalyOrder order by PostedDate desc) as Id, DispalyOrder,ImageName,PostedDate from a
where PostedDate <='2013-01-11'
) as a
where Id=1

快乐编码！

：）

Happy Coding!
:)

这篇关于SQL查询或SP问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！