sql查询问题
0
悬赏园豆：10
[已解决问题]
解决于 2011-04-11 14:33
表A: tid,title
表B: id,tid,date,price
说明：表A是各个旅游线路信息，表B是各个线路计划出团日期和价格
现在要实现选出各个旅游线路最近一天的出团信息，包括表A、B的所有字段信息
最佳答案
0
IF NOT OBJECT_ID('[AAA]') IS NULL
DROP TABLE [AAA]
GO
CREATE TABLE [AAA](
[tid] int identity(1,1) primary key,
[title] NVARCHAR(100))
go
INSERT [AAA]
SELECT '西安' UNION ALL
SELECT '杭州' UNION ALL
SELECT '日本福冈'
GO
----select * from AAA
/*
1 西安
2 杭州
3 日本福冈
*/
IF NOT OBJECT_ID('[BBB]') IS NULL
DROP TABLE [BBB]
GO
CREATE TABLE [BBB](
[id] int identity(1,1) primary key,
[tid] int null,
[Date] dateTime,
[Price] Decimal(15,0)
)
go
INSERT [BBB]
SELECT 1,'2011-3-3',3400 UNION ALL
SELECT 1,'2011-3-10',2300 UNION ALL
SELECT 2,'2011-3-12',2000 UNION ALL
SELECT 3,'2011-3-11',100 UNION ALL
SELECT 3,'2011-3-13',30 UNION ALL
SELECT 3,'2011-3-14',50 UNION ALL
SELECT 3,'2011-3-21',40
GO
--select * from BBB
/*
id tid Date Price
1 1 2011-03-03 00:00:00.000 3400
2 1 2011-03-10 00:00:00.000 2300
3 2 2011-03-12 00:00:00.000 2000
4 3 2011-03-11 00:00:00.000 100
5 3 2011-03-13 00:00:00.000 30
6 3 2011-03-14 00:00:00.000 50
7 3 2011-03-21 00:00:00.000 40
*/
Select AAA.*,BBB.* from BBB Inner join AAA on AAA.TID=BBB.TID
and [ID] in
(SELECT [ID] FROM BBB T
WHERE [date]=(SELECT Max([Date]) FROM BBB
WHERE BBB.TID=T.TID)
)
/*
tid title id tid Date Price
3 日本福冈 7 3 2011-03-21 00:00:00.000 40
2 杭州 3 2 2011-03-12 00:00:00.000 2000
1 西安 2 1 2011-03-10 00:00:00.000 2300
*/
收获园豆：10
其他回答(1)
0
select tid,(select title from tablea where tid=b.tid) as title,max(date) ,price from tableb a group by tid,title,price