求一SQL,大家帮帮忙.

qingtian6 2009-10-30 11:27:15
ID 日期 出发城市 到达城市 价格
2 2009-11-20 SZX WUH 740
3 2009-11-21 SZX WUH 690
4 2009-11-22 SZX WUH 690
5 2009-11-23 SZX WUH 540
6 2009-11-24 SZX WUH 440
7 2009-11-25 SZX WUH 440
8 2009-11-26 SZX WUH 390
9 2009-11-20 SZX SHA 1190
10 2009-11-21 SZX SHA 1120
11 2009-11-22 SZX SHA 840
12 2009-11-23 SZX SHA 840
13 2009-11-24 SZX SHA 700
14 2009-11-25 SZX SHA 700
15 2009-11-26 SZX SHA 630
16 2009-11-20 SZX PEK 1750
17 2009-11-21 SZX PEK 1750
18 2009-11-22 SZX PEK 1750
19 2009-11-23 SZX PEK 1750
20 2009-11-24 SZX PEK 1750
21 2009-11-25 SZX PEK 1750
22 2009-11-26 SZX PEK 1400
23 2009-11-20 SZX TSN 1850
24 2009-11-21 SZX TSN 1850
25 2009-11-22 SZX TSN 1390
26 2009-11-23 SZX TSN 1110
27 2009-11-24 SZX TSN 1110
28 2009-11-25 SZX TSN 1110
29 2009-11-26 SZX TSN 830
30 2009-11-20 SZX CKG 960
31 2009-11-21 SZX CKG 960
32 2009-11-22 SZX CKG 960
33 2009-11-23 SZX CKG 960
34 2009-11-24 SZX CKG 580
35 2009-11-25 SZX CKG 580
36 2009-11-26 SZX CKG 580
37 2009-11-20 SZX CTU 1060
38 2009-11-21 SZX CTU 1060
39 2009-11-22 SZX CTU 1060
40 2009-11-23 SZX CTU 1060
41 2009-11-24 SZX CTU 1060
42 2009-11-25 SZX CTU 850
43 2009-11-26 SZX CTU 850
44 2009-11-20 SZX CSX 400
45 2009-11-21 SZX CSX 440
46 2009-11-22 SZX CSX 370
47 2009-11-23 SZX CSX 400
48 2009-11-24 SZX CSX 370
49 2009-11-25 SZX CSX 370
50 2009-11-26 SZX CSX 370
49 2009-11-25 PEK WUH 650
50 2009-11-26 PEK WUH 580
49 2009-11-25 PEK CTU 650
50 2009-11-26 PEK SZX 880

说明：出发城市、到达城市相同为同一条航线。
需求：查询出每一条航线在未来一段时间内（比如果15天）的价格最低的3条数据。以日期排序
结果示例：

6 2009-11-24 SZX WUH 440
7 2009-11-25 SZX WUH 440
8 2009-11-26 SZX WUH 390
13 2009-11-24 SZX SHA 700
14 2009-11-25 SZX SHA 700
15 2009-11-26 SZX SHA 630
16 2009-11-20 SZX PEK 1750
17 2009-11-21 SZX PEK 1750
22 2009-11-26 SZX PEK 1400
省略…………
49 2009-11-25 PEK WUH 650
50 2009-11-26 PEK WUH 580
49 2009-11-25 PEK CTU 650
50 2009-11-26 PEK SZX 880
...全文
196 32 打赏 收藏 转发到动态 举报
写回复
用AI写文章
32 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复
还是只有3条啊
qingtian6 2009-10-30
  • 打赏
  • 举报
 回复
小F，6楼的结果有点问题，当日期有重复的时候，结果数据也会多.
比如在你的测试数据里加一条
select 80,'2009-11-26','SZX','WUH',400 union all
这样 SZX-WUH的结果数据就会有4条,麻烦再看看,我改不好.
qingtian6 2009-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 fredrickhu 的回复:]
引用 13 楼 qingtian6 的回复:
还有个问题，同一条航线 同一天只要一条数据.大家再帮帮忙.



6楼的可以满足你的要求
[/Quote]
嗯，谢谢小F
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 qingtian6 的回复:]
还有个问题，同一条航线 同一天只要一条数据.大家再帮帮忙.
[/Quote]


6楼的可以满足你的要求
qingtian6 2009-10-30
  • 打赏
  • 举报
 回复
吃饭先,大家也去吃饭吧,等下再弄.
qingtian6 2009-10-30
  • 打赏
  • 举报
 回复
还有个问题，同一条航线 同一天只要一条数据.大家再帮帮忙.
guguda2008 2009-10-30
  • 打赏
  • 举报
 回复
如果是2005的话直接用ROW_NUMBER()分组排序
lounddong 2009-10-30
  • 打赏
  • 举报
 回复 


if object_id('flight') is not null

drop table flight

create table flight (ID int,日期 char(10),出发城市 nvarchar(30), 到达城市 nvarchar(30), 价格 dec(18,2))

insert into flight

select 2,'2009-11-20','SZX','WUH',740 union all

select 3,'2009-11-21','SZX','WUH',690 union all

select 4,'2009-11-22','SZX','WUH',190 union all

select 5,'2009-11-23','SZX','WUH',540 union all

select 6,'2009-11-24','SZX','WUH',440 union all

select 7,'2009-11-25','SZX','WUH',340 union all

select 8,'2009-11-26','SZX','WUH',390 union all

select 9,'2009-11-20','SZX','SHA',1190 union all

select 10,'2009-11-21','SZX','SHA',1120 union all

select 11,'2009-11-22','SZX','SHA',840 union all

select 12,'2009-11-23','SZX','SHA',840 union all

select 13,'2009-11-24','SZX','SHA',700 union all

select 14,'2009-11-25','SZX','SHA',700 union all

select 15,'2009-11-26','SZX','SHA',630 union all

select 16,'2009-11-20','SZX','PEK',1750 union all

select 17,'2009-11-21','SZX','PEK',1750 union all

select 18,'2009-11-22','SZX','PEK',1750 union all

select 19,'2009-11-23','SZX','PEK',1750 union all

select 20,'2009-11-24','SZX','PEK',1750 union all

select 21,'2009-11-25','SZX','PEK',1750 union all

select 22,'2009-11-26','SZX','PEK',1400 union all

select 23,'2009-11-20','SZX','TSN',1850 union all

select 24,'2009-11-21','SZX','TSN',1850 union all

select 25,'2009-11-22','SZX','TSN',1390 union all

select 26,'2009-11-23','SZX','TSN',1110 union all

select 27,'2009-11-24','SZX','TSN',1110 union all

select 28,'2009-11-25','SZX','TSN',1110 union all

select 29,'2009-11-26','SZX','TSN',830 union all

select 30,'2009-11-20','SZX','CKG',960 union all

select 31,'2009-11-21','SZX','CKG',960 union all

select 32,'2009-11-22','SZX','CKG',960 union all

select 33,'2009-11-23','SZX','CKG',960 union all

select 34,'2009-11-24','SZX','CKG',580 union all

select 35,'2009-11-25','SZX','CKG',580 union all

select 36,'2009-11-26','SZX','CKG',580 union all

select 37,'2009-11-20','SZX','CTU',1060 union all

select 38,'2009-11-21','SZX','CTU',1060 union all

select 39,'2009-11-22','SZX','CTU',1060 union all

select 40,'2009-11-23','SZX','CTU',1060 union all

select 41,'2009-11-24','SZX','CTU',1060 union all

select 42,'2009-11-25','SZX','CTU',850 union all

select 43,'2009-11-26','SZX','CTU',850 union all

select 44,'2009-11-20','SZX','CSX',400 union all

select 45,'2009-11-21','SZX','CSX',440 union all

select 46,'2009-11-22','SZX','CSX',370 union all

select 47,'2009-11-23','SZX','CSX',400 union all

select 48,'2009-11-24','SZX','CSX',370 union all

select 49,'2009-11-25','SZX','CSX',370 union all

select 50,'2009-11-26','SZX','CSX',370 union all

select 49,'2009-11-25','PEK','WUH',650 union all

select 50,'2009-11-26','PEK','WUH',580 union all

select 49,'2009-11-25','PEK','CTU',650 union all

select 50,'2009-11-26','PEK','SZX',880



select * from flight t

where id in(select top 3 id from flight where t.出发城市+t.到达城市 = 出发城市+到达城市 order by 价格,日期 desc )

--结果

4	2009-11-22	SZX	WUH	190.00

7	2009-11-25	SZX	WUH	340.00

8	2009-11-26	SZX	WUH	390.00

13	2009-11-24	SZX	SHA	700.00

14	2009-11-25	SZX	SHA	700.00

15	2009-11-26	SZX	SHA	630.00

20	2009-11-24	SZX	PEK	1750.00

21	2009-11-25	SZX	PEK	1750.00

22	2009-11-26	SZX	PEK	1400.00

27	2009-11-24	SZX	TSN	1110.00

28	2009-11-25	SZX	TSN	1110.00

29	2009-11-26	SZX	TSN	830.00

34	2009-11-24	SZX	CKG	580.00

35	2009-11-25	SZX	CKG	580.00

36	2009-11-26	SZX	CKG	580.00

41	2009-11-24	SZX	CTU	1060.00

42	2009-11-25	SZX	CTU	850.00

43	2009-11-26	SZX	CTU	850.00

48	2009-11-24	SZX	CSX	370.00

49	2009-11-25	SZX	CSX	370.00

50	2009-11-26	SZX	CSX	370.00

49	2009-11-25	PEK	WUH	650.00

50	2009-11-26	PEK	WUH	580.00

49	2009-11-25	PEK	CTU	650.00

50	2009-11-26	PEK	SZX	880.00
gsk09 2009-10-30
  • 打赏
  • 举报
 回复 


--> Test data : @t

declare @t table ([ID] int,[日期] datetime,[出城市] varchar(3),[到城市] varchar(3),[价格] int)

insert into @t

select 2,'2009-11-20','SZX','WUH',740 union all

select 3,'2009-11-21','SZX','WUH',690 union all

select 4,'2009-11-22','SZX','WUH',690 union all

select 5,'2009-11-23','SZX','WUH',540 union all

select 6,'2009-11-24','SZX','WUH',440 union all

select 7,'2009-11-25','SZX','WUH',440 union all

select 8,'2009-11-26','SZX','WUH',390 union all

select 9,'2009-11-20','SZX','SHA',1190 union all

select 10,'2009-11-21','SZX','SHA',1120 union all

select 11,'2009-11-22','SZX','SHA',840 union all

select 12,'2009-11-23','SZX','SHA',840 union all

select 13,'2009-11-24','SZX','SHA',700 union all

select 14,'2009-11-25','SZX','SHA',700 union all

select 15,'2009-11-26','SZX','SHA',630 union all

select 16,'2009-11-20','SZX','PEK',1750 union all

select 17,'2009-11-21','SZX','PEK',1750 union all

select 18,'2009-11-22','SZX','PEK',1750 union all

select 19,'2009-11-23','SZX','PEK',1750 union all

select 20,'2009-11-24','SZX','PEK',1750 union all

select 21,'2009-11-25','SZX','PEK',1750 union all

select 22,'2009-11-26','SZX','PEK',1400 union all

select 23,'2009-11-20','SZX','TSN',1850 union all

select 24,'2009-11-21','SZX','TSN',1850 union all

select 25,'2009-11-22','SZX','TSN',1390 union all

select 26,'2009-11-23','SZX','TSN',1110 union all

select 27,'2009-11-24','SZX','TSN',1110 union all

select 28,'2009-11-25','SZX','TSN',1110 union all

select 29,'2009-11-26','SZX','TSN',830 union all

select 30,'2009-11-20','SZX','CKG',960 union all

select 31,'2009-11-21','SZX','CKG',960 union all

select 32,'2009-11-22','SZX','CKG',960 union all

select 33,'2009-11-23','SZX','CKG',960 union all

select 34,'2009-11-24','SZX','CKG',580 union all

select 35,'2009-11-25','SZX','CKG',580 union all

select 36,'2009-11-26','SZX','CKG',580 union all

select 37,'2009-11-20','SZX','CTU',1060 union all

select 38,'2009-11-21','SZX','CTU',1060 union all

select 39,'2009-11-22','SZX','CTU',1060 union all

select 40,'2009-11-23','SZX','CTU',1060 union all

select 41,'2009-11-24','SZX','CTU',1060 union all

select 42,'2009-11-25','SZX','CTU',850 union all

select 43,'2009-11-26','SZX','CTU',850 union all

select 44,'2009-11-20','SZX','CSX',400 union all

select 45,'2009-11-21','SZX','CSX',440 union all

select 46,'2009-11-22','SZX','CSX',370 union all

select 47,'2009-11-23','SZX','CSX',400 union all

select 48,'2009-11-24','SZX','CSX',370 union all

select 49,'2009-11-25','SZX','CSX',370 union all

select 50,'2009-11-26','SZX','CSX',370 union all

select 49,'2009-11-25','PEK','WUH',650 union all

select 50,'2009-11-26','PEK','WUH',580 union all

select 49,'2009-11-25','PEK','CTU',650 union all

select 50,'2009-11-26','PEK','SZX',880



declare @startDay datetime,@endDay datetime

select @startDay='2009-11-20',@endDay='2009-12-21'--设置始末日期



select 

	t.[ID],t.日期,t.出城市,t.到城市,t.价格

from 

	(select *	

		,[出城市]+[到城市] od

		,row_number()over(partition by [出城市],[到城市] order by [日期])idx

	from @t

	where [日期] between @startDay and @endDay

	)t

where t.idx<=3

order by t.od,t.日期





ID          日期                      出城市  到城市  价格

----------- ----------------------- ---- ---- -----------

49          2009-11-25 00:00:00.000 PEK  CTU  650

50          2009-11-26 00:00:00.000 PEK  SZX  880

49          2009-11-25 00:00:00.000 PEK  WUH  650

50          2009-11-26 00:00:00.000 PEK  WUH  580

30          2009-11-20 00:00:00.000 SZX  CKG  960

31          2009-11-21 00:00:00.000 SZX  CKG  960

32          2009-11-22 00:00:00.000 SZX  CKG  960

44          2009-11-20 00:00:00.000 SZX  CSX  400

45          2009-11-21 00:00:00.000 SZX  CSX  440

46          2009-11-22 00:00:00.000 SZX  CSX  370

37          2009-11-20 00:00:00.000 SZX  CTU  1060

38          2009-11-21 00:00:00.000 SZX  CTU  1060

39          2009-11-22 00:00:00.000 SZX  CTU  1060

16          2009-11-20 00:00:00.000 SZX  PEK  1750

17          2009-11-21 00:00:00.000 SZX  PEK  1750

18          2009-11-22 00:00:00.000 SZX  PEK  1750

9           2009-11-20 00:00:00.000 SZX  SHA  1190

10          2009-11-21 00:00:00.000 SZX  SHA  1120

11          2009-11-22 00:00:00.000 SZX  SHA  840

23          2009-11-20 00:00:00.000 SZX  TSN  1850

24          2009-11-21 00:00:00.000 SZX  TSN  1850

25          2009-11-22 00:00:00.000 SZX  TSN  1390

2           2009-11-20 00:00:00.000 SZX  WUH  740

3           2009-11-21 00:00:00.000 SZX  WUH  690

4           2009-11-22 00:00:00.000 SZX  WUH  690
qingtian6 2009-10-30
  • 打赏
  • 举报
 回复
谢谢你们。都很强大。
lounddong 2009-10-30
  • 打赏
  • 举报
 回复
价格最低的3条数据。。。
jwdream2008 2009-10-30
  • 打赏
  • 举报
 回复 


--上一个忘记日期排序了：

select ID,日期,出发城市,到达城市,价格 from 

(select * ,row_number() over(partition by 出发城市 ,到达城市 order by 价格 )as [order] from Table

 where  datediff(dd,getdate(),'2009-10-31')<16 ) as T

where T.[order]<4 order by 日期
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复 
---或者

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-10-30 11:29:36

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([ID] int,[日期] datetime,[出发城市] varchar(3),[到达城市] varchar(3),[价格] int)

insert [tb]

select 2,'2009-11-20','SZX','WUH',740 union all

select 3,'2009-11-21','SZX','WUH',690 union all

select 4,'2009-11-22','SZX','WUH',690 union all

select 5,'2009-11-23','SZX','WUH',540 union all

select 6,'2009-11-24','SZX','WUH',440 union all

select 7,'2009-11-25','SZX','WUH',440 union all

select 8,'2009-11-26','SZX','WUH',390 union all

select 9,'2009-11-20','SZX','SHA',1190 union all

select 10,'2009-11-21','SZX','SHA',1120 union all

select 11,'2009-11-22','SZX','SHA',840 union all

select 12,'2009-11-23','SZX','SHA',840 union all

select 13,'2009-11-24','SZX','SHA',700 union all

select 14,'2009-11-25','SZX','SHA',700 union all

select 15,'2009-11-26','SZX','SHA',630 union all

select 16,'2009-11-20','SZX','PEK',1750 union all

select 17,'2009-11-21','SZX','PEK',1750 union all

select 18,'2009-11-22','SZX','PEK',1750 union all

select 19,'2009-11-23','SZX','PEK',1750 union all

select 20,'2009-11-24','SZX','PEK',1750 union all

select 21,'2009-11-25','SZX','PEK',1750 union all

select 22,'2009-11-26','SZX','PEK',1400 union all

select 23,'2009-11-20','SZX','TSN',1850 union all

select 24,'2009-11-21','SZX','TSN',1850 union all

select 25,'2009-11-22','SZX','TSN',1390 union all

select 26,'2009-11-23','SZX','TSN',1110 union all

select 27,'2009-11-24','SZX','TSN',1110 union all

select 28,'2009-11-25','SZX','TSN',1110 union all

select 29,'2009-11-26','SZX','TSN',830 union all

select 30,'2009-11-20','SZX','CKG',960 union all

select 31,'2009-11-21','SZX','CKG',960 union all

select 32,'2009-11-22','SZX','CKG',960 union all

select 33,'2009-11-23','SZX','CKG',960 union all

select 34,'2009-11-24','SZX','CKG',580 union all

select 35,'2009-11-25','SZX','CKG',580 union all

select 36,'2009-11-26','SZX','CKG',580 union all

select 37,'2009-11-20','SZX','CTU',1060 union all

select 38,'2009-11-21','SZX','CTU',1060 union all

select 39,'2009-11-22','SZX','CTU',1060 union all

select 40,'2009-11-23','SZX','CTU',1060 union all

select 41,'2009-11-24','SZX','CTU',1060 union all

select 42,'2009-11-25','SZX','CTU',850 union all

select 43,'2009-11-26','SZX','CTU',850 union all

select 44,'2009-11-20','SZX','CSX',400 union all

select 45,'2009-11-21','SZX','CSX',440 union all

select 46,'2009-11-22','SZX','CSX',370 union all

select 47,'2009-11-23','SZX','CSX',400 union all

select 48,'2009-11-24','SZX','CSX',370 union all

select 49,'2009-11-25','SZX','CSX',370 union all

select 50,'2009-11-26','SZX','CSX',370 union all

select 49,'2009-11-25','PEK','WUH',650 union all

select 50,'2009-11-26','PEK','WUH',580 union all

select 49,'2009-11-25','PEK','CTU',650 union all

select 50,'2009-11-26','PEK','SZX',880

--------------开始查询--------------------------

SELECT

   *

FROM

   TB AS T 

WHERE

   3>(SELECT COUNT(1) FROM TB WHERE 出发城市=T.出发城市 and 到达城市=T.到达城市 AND 日期>T.日期)

and

  datediff(dd,'2009-11-20',日期)<=15

----------------结果----------------------------

/* ID          日期                      出发城市 到达城市 价格

----------- ----------------------- ---- ---- -----------

6           2009-11-24 00:00:00.000 SZX  WUH  440

7           2009-11-25 00:00:00.000 SZX  WUH  440

8           2009-11-26 00:00:00.000 SZX  WUH  390

13          2009-11-24 00:00:00.000 SZX  SHA  700

14          2009-11-25 00:00:00.000 SZX  SHA  700

15          2009-11-26 00:00:00.000 SZX  SHA  630

20          2009-11-24 00:00:00.000 SZX  PEK  1750

21          2009-11-25 00:00:00.000 SZX  PEK  1750

22          2009-11-26 00:00:00.000 SZX  PEK  1400

27          2009-11-24 00:00:00.000 SZX  TSN  1110

28          2009-11-25 00:00:00.000 SZX  TSN  1110

29          2009-11-26 00:00:00.000 SZX  TSN  830

34          2009-11-24 00:00:00.000 SZX  CKG  580

35          2009-11-25 00:00:00.000 SZX  CKG  580

36          2009-11-26 00:00:00.000 SZX  CKG  580

41          2009-11-24 00:00:00.000 SZX  CTU  1060

42          2009-11-25 00:00:00.000 SZX  CTU  850

43          2009-11-26 00:00:00.000 SZX  CTU  850

48          2009-11-24 00:00:00.000 SZX  CSX  370

49          2009-11-25 00:00:00.000 SZX  CSX  370

50          2009-11-26 00:00:00.000 SZX  CSX  370

49          2009-11-25 00:00:00.000 PEK  WUH  650

50          2009-11-26 00:00:00.000 PEK  WUH  580

49          2009-11-25 00:00:00.000 PEK  CTU  650

50          2009-11-26 00:00:00.000 PEK  SZX  880



(25 行受影响)



*/
jwdream2008 2009-10-30
  • 打赏
  • 举报
 回复 
select ID,日期,出发城市,到达城市,价格 from 

(select * ,row_number() over(partition by 出发城市 ,到达城市 order by 价格 )as [order] from Table

 where  datediff(dd,getdate(),'2009-10-31')<16 ) as T

where T.[order]<4
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-10-30 11:29:36

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([ID] int,[日期] datetime,[出发城市] varchar(3),[到达城市] varchar(3),[价格] int)

insert [tb]

select 2,'2009-11-20','SZX','WUH',740 union all

select 3,'2009-11-21','SZX','WUH',690 union all

select 4,'2009-11-22','SZX','WUH',690 union all

select 5,'2009-11-23','SZX','WUH',540 union all

select 6,'2009-11-24','SZX','WUH',440 union all

select 7,'2009-11-25','SZX','WUH',440 union all

select 8,'2009-11-26','SZX','WUH',390 union all

select 9,'2009-11-20','SZX','SHA',1190 union all

select 10,'2009-11-21','SZX','SHA',1120 union all

select 11,'2009-11-22','SZX','SHA',840 union all

select 12,'2009-11-23','SZX','SHA',840 union all

select 13,'2009-11-24','SZX','SHA',700 union all

select 14,'2009-11-25','SZX','SHA',700 union all

select 15,'2009-11-26','SZX','SHA',630 union all

select 16,'2009-11-20','SZX','PEK',1750 union all

select 17,'2009-11-21','SZX','PEK',1750 union all

select 18,'2009-11-22','SZX','PEK',1750 union all

select 19,'2009-11-23','SZX','PEK',1750 union all

select 20,'2009-11-24','SZX','PEK',1750 union all

select 21,'2009-11-25','SZX','PEK',1750 union all

select 22,'2009-11-26','SZX','PEK',1400 union all

select 23,'2009-11-20','SZX','TSN',1850 union all

select 24,'2009-11-21','SZX','TSN',1850 union all

select 25,'2009-11-22','SZX','TSN',1390 union all

select 26,'2009-11-23','SZX','TSN',1110 union all

select 27,'2009-11-24','SZX','TSN',1110 union all

select 28,'2009-11-25','SZX','TSN',1110 union all

select 29,'2009-11-26','SZX','TSN',830 union all

select 30,'2009-11-20','SZX','CKG',960 union all

select 31,'2009-11-21','SZX','CKG',960 union all

select 32,'2009-11-22','SZX','CKG',960 union all

select 33,'2009-11-23','SZX','CKG',960 union all

select 34,'2009-11-24','SZX','CKG',580 union all

select 35,'2009-11-25','SZX','CKG',580 union all

select 36,'2009-11-26','SZX','CKG',580 union all

select 37,'2009-11-20','SZX','CTU',1060 union all

select 38,'2009-11-21','SZX','CTU',1060 union all

select 39,'2009-11-22','SZX','CTU',1060 union all

select 40,'2009-11-23','SZX','CTU',1060 union all

select 41,'2009-11-24','SZX','CTU',1060 union all

select 42,'2009-11-25','SZX','CTU',850 union all

select 43,'2009-11-26','SZX','CTU',850 union all

select 44,'2009-11-20','SZX','CSX',400 union all

select 45,'2009-11-21','SZX','CSX',440 union all

select 46,'2009-11-22','SZX','CSX',370 union all

select 47,'2009-11-23','SZX','CSX',400 union all

select 48,'2009-11-24','SZX','CSX',370 union all

select 49,'2009-11-25','SZX','CSX',370 union all

select 50,'2009-11-26','SZX','CSX',370 union all

select 49,'2009-11-25','PEK','WUH',650 union all

select 50,'2009-11-26','PEK','WUH',580 union all

select 49,'2009-11-25','PEK','CTU',650 union all

select 50,'2009-11-26','PEK','SZX',880

--------------开始查询--------------------------

SELECT

  * 

FROM

 TB AS T 

WHERE

  ID 

IN

  (SELECT TOP 3 ID FROM TB WHERE 出发城市=T.出发城市 and 到达城市=t.到达城市  ORDER BY 日期 DESC)

and

  datediff(dd,'2009-11-20',日期)<=15

----------------结果----------------------------

/* ID          日期                      出发城市 到达城市 价格

----------- ----------------------- ---- ---- -----------

6           2009-11-24 00:00:00.000 SZX  WUH  440

7           2009-11-25 00:00:00.000 SZX  WUH  440

8           2009-11-26 00:00:00.000 SZX  WUH  390

13          2009-11-24 00:00:00.000 SZX  SHA  700

14          2009-11-25 00:00:00.000 SZX  SHA  700

15          2009-11-26 00:00:00.000 SZX  SHA  630

20          2009-11-24 00:00:00.000 SZX  PEK  1750

21          2009-11-25 00:00:00.000 SZX  PEK  1750

22          2009-11-26 00:00:00.000 SZX  PEK  1400

27          2009-11-24 00:00:00.000 SZX  TSN  1110

28          2009-11-25 00:00:00.000 SZX  TSN  1110

29          2009-11-26 00:00:00.000 SZX  TSN  830

34          2009-11-24 00:00:00.000 SZX  CKG  580

35          2009-11-25 00:00:00.000 SZX  CKG  580

36          2009-11-26 00:00:00.000 SZX  CKG  580

41          2009-11-24 00:00:00.000 SZX  CTU  1060

42          2009-11-25 00:00:00.000 SZX  CTU  850

43          2009-11-26 00:00:00.000 SZX  CTU  850

48          2009-11-24 00:00:00.000 SZX  CSX  370

49          2009-11-25 00:00:00.000 SZX  CSX  370

50          2009-11-26 00:00:00.000 SZX  CSX  370

49          2009-11-25 00:00:00.000 PEK  WUH  650

50          2009-11-26 00:00:00.000 PEK  WUH  580

49          2009-11-25 00:00:00.000 PEK  CTU  650

50          2009-11-26 00:00:00.000 PEK  SZX  880



(25 行受影响)



*/
jiangshun 2009-10-30
  • 打赏
  • 举报
 回复 


--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([ID] int,[日期] datetime,[出发城市] varchar(3),[到达城市] varchar(3),[价格] int)

insert [TB]

select 2,'2009-11-20','SZX','WUH',740 union all

select 3,'2009-11-21','SZX','WUH',690 union all

select 4,'2009-11-22','SZX','WUH',690 union all

select 5,'2009-11-23','SZX','WUH',540 union all

select 6,'2009-11-24','SZX','WUH',440 union all

select 7,'2009-11-25','SZX','WUH',440 union all

select 8,'2009-11-26','SZX','WUH',390 union all

select 9,'2009-11-20','SZX','SHA',1190 union all

select 10,'2009-11-21','SZX','SHA',1120 union all

select 11,'2009-11-22','SZX','SHA',840 union all

select 12,'2009-11-23','SZX','SHA',840 union all

select 13,'2009-11-24','SZX','SHA',700 union all

select 14,'2009-11-25','SZX','SHA',700 union all

select 15,'2009-11-26','SZX','SHA',630 union all

select 16,'2009-11-20','SZX','PEK',1750 union all

select 17,'2009-11-21','SZX','PEK',1750 union all

select 18,'2009-11-22','SZX','PEK',1750 union all

select 19,'2009-11-23','SZX','PEK',1750 union all

select 20,'2009-11-24','SZX','PEK',1750 union all

select 21,'2009-11-25','SZX','PEK',1750 union all

select 22,'2009-11-26','SZX','PEK',1400 union all

select 23,'2009-11-20','SZX','TSN',1850 union all

select 24,'2009-11-21','SZX','TSN',1850 union all

select 25,'2009-11-22','SZX','TSN',1390 union all

select 26,'2009-11-23','SZX','TSN',1110 union all

select 27,'2009-11-24','SZX','TSN',1110 union all

select 28,'2009-11-25','SZX','TSN',1110 union all

select 29,'2009-11-26','SZX','TSN',830 union all

select 30,'2009-11-20','SZX','CKG',960 union all

select 31,'2009-11-21','SZX','CKG',960 union all

select 32,'2009-11-22','SZX','CKG',960 union all

select 33,'2009-11-23','SZX','CKG',960 union all

select 34,'2009-11-24','SZX','CKG',580 union all

select 35,'2009-11-25','SZX','CKG',580 union all

select 36,'2009-11-26','SZX','CKG',580 union all

select 37,'2009-11-20','SZX','CTU',1060 union all

select 38,'2009-11-21','SZX','CTU',1060 union all

select 39,'2009-11-22','SZX','CTU',1060 union all

select 40,'2009-11-23','SZX','CTU',1060 union all

select 41,'2009-11-24','SZX','CTU',1060 union all

select 42,'2009-11-25','SZX','CTU',850 union all

select 43,'2009-11-26','SZX','CTU',850 union all

select 44,'2009-11-20','SZX','CSX',400 union all

select 45,'2009-11-21','SZX','CSX',440 union all

select 46,'2009-11-22','SZX','CSX',370 union all

select 47,'2009-11-23','SZX','CSX',400 union all

select 48,'2009-11-24','SZX','CSX',370 union all

select 49,'2009-11-25','SZX','CSX',370 union all

select 50,'2009-11-26','SZX','CSX',370 union all

select 49,'2009-11-25','PEK','WUH',650 union all

select 50,'2009-11-26','PEK','WUH',580 union all

select 49,'2009-11-25','PEK','CTU',650 union all

select 50,'2009-11-26','PEK','SZX',880



select * from [TB] t where iD 

in(select top 3 ID from TB where t.出发城市+t.到达城市=出发城市+到达城市 order by 日期 desc)



/*

ID          日期                                                     出发城市 到达城市 价格          

----------- ------------------------------------------------------ ---- ---- ----------- 

6           2009-11-24 00:00:00.000                                SZX  WUH  440

7           2009-11-25 00:00:00.000                                SZX  WUH  440

8           2009-11-26 00:00:00.000                                SZX  WUH  390

13          2009-11-24 00:00:00.000                                SZX  SHA  700

14          2009-11-25 00:00:00.000                                SZX  SHA  700

15          2009-11-26 00:00:00.000                                SZX  SHA  630

20          2009-11-24 00:00:00.000                                SZX  PEK  1750

21          2009-11-25 00:00:00.000                                SZX  PEK  1750

22          2009-11-26 00:00:00.000                                SZX  PEK  1400

27          2009-11-24 00:00:00.000                                SZX  TSN  1110

28          2009-11-25 00:00:00.000                                SZX  TSN  1110

29          2009-11-26 00:00:00.000                                SZX  TSN  830

34          2009-11-24 00:00:00.000                                SZX  CKG  580

35          2009-11-25 00:00:00.000                                SZX  CKG  580

36          2009-11-26 00:00:00.000                                SZX  CKG  580

41          2009-11-24 00:00:00.000                                SZX  CTU  1060

42          2009-11-25 00:00:00.000                                SZX  CTU  850

43          2009-11-26 00:00:00.000                                SZX  CTU  850

48          2009-11-24 00:00:00.000                                SZX  CSX  370

49          2009-11-25 00:00:00.000                                SZX  CSX  370

50          2009-11-26 00:00:00.000                                SZX  CSX  370

49          2009-11-25 00:00:00.000                                PEK  WUH  650

50          2009-11-26 00:00:00.000                                PEK  WUH  580

49          2009-11-25 00:00:00.000                                PEK  CTU  650

50          2009-11-26 00:00:00.000                                PEK  SZX  880



（所影响的行数为 25 行）



*/



drop table TB
jiangshun 2009-10-30
  • 打赏
  • 举报
 回复 
select * from [TB] t where iD in(select top 3 ID from TB where t.出发城市+t.到达城市=出发城市+到达城市 order by 日期 desc)
guguda2008 2009-10-30
  • 打赏
  • 举报
 回复
我是来蹭分的
qingtian6 2009-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 31 楼 zs621 的回复:]
SQL codeselect*from tb bwhere idin(selecttop3 idfrom (select*from tb twherenotexists(select1from tbwhere 日期=t.日期and 出发城市=t.出发城市and 到达城市=t.到达城市and 价格=t.价格and id>t.id)andnotexists(select1from tbwhere 日期¡­
[/Quote]
这个结果是对的。谢谢。
zs621 2009-10-30
  • 打赏
  • 举报
 回复 


select * from tb b where id in(

select top 3 id from (

select * from tb t where

not exists(select 1 from tb where 日期=t.日期 and 出发城市=t.出发城市 and 到达城市=t.到达城市 and 价格=t.价格 and id>t.id)

and not exists(select 1 from tb where 日期=t.日期 and 出发城市=t.出发城市 and 到达城市=t.到达城市 and 价格<t.价格 and id<>t.id)

)c where b.出发城市+b.到达城市=出发城市+到达城市 order by 价格

) order by 出发城市,到达城市
加载更多回复（12）
hibernate连接池配置及SQLJDBC.jar JNDA.JAR
资源包括hibernate Sql server2005连接池配置源码，及sqlserverjdbc.jar jTDS3.jar jtds-1.2.jar ojdbc14.jar proxool-0.9.0RC3.jar 等jar包，希望能帮大家一些忙
为 SQL 2005 SQLEXPRESS 添加sa 帐户(图解教程)
用过VS2005的人知道在安装后自动安装了SQL 2005 SQLEXPRESS 可惜没有管理器无法操作,本教程添加了SQL 2005管理器的链接并为用户添加了sa用户,大家知道sa帐户在sql 7和sql 2000中被广泛使用.希望本教程可以帮大家的忙.
SQl Server数据库
讲述Sql Server数据库的企业级开发常用的关键知识点。尽量把概念讲的通俗易懂，了解其架构原理，不仅仅会操作应用，还要把握其本质。帮大家更深层次的理解数据库。【课程目录】一.Sql Server数据库架构1.架构2.体系结构协议层（Protocols）/•关系引擎（Relational Engine），也称为查询处理器（Query Processor）/存储引擎（Storage Engine）/•SQLOS二.数据库核心应用。1.数据仓库/2.连接数据库/3.数据库开发三.数据库设计方法。四.SQL语句。1.Sql基本操作。/2.数据库库表资源获取。五.数据库事务。1.事务访问数据库。/2.数据批处理
weblogic连接sql.doc
请各位大侠帮帮忙啊
将SQLSEVER中的表根据条件导出为EXCEL文件_fixed
帮C友一个忙： 贴子： http://bbs.csdn.net/topics/392215294 博客： http://blog.csdn.net/yenange/article/details/77164587 最终可以用 windows 的计划任务实现定时导出。
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章