日期:2014-05-17  浏览次数:20386 次

问个sql查询的问题
现有两张表
表taritem是收费项目表
tr_rowid tr_item_code tr_item_desc
1000 xy100323 阿莫西林
1001 xy200365 葡糖糖
表tr_price表是价格表
tp_rowid tr_rowid tp_startdate tp_price
1000||1 1000 2001-09-08 10
1000||2 1000 2002-01-05 11.5
1001||1 1001 2003-05-08 50
1001||2 1001 2005-01-05 50.5  
1001||3 1001 2008-04-25 52
两张表是通过tr_rowid关联的,sql怎么写能把价格表里最近的开始日期tp_startdate对应的价格取出来  
   
得到如下结果
tr_rowid tr_item_code tr_item_desc tp_startdate tp_price
1000 xy100323 阿莫西林 2002-01-05 11.5
1001 xy200365 葡糖糖 2008-04-25 52
如果在程序里中处理是不难的,但我想在数据库sql文里直接查询得到上述结果该怎么写啊?

------解决方案--------------------
SQL code

select taritem.*,t1.tp_startdate,t1.tp_price from taritem,
    (select row_number() over(partition by tr_rowid order by tp_startdate desc) rn,
    tr_rowid,tp_startdate,tp_price from tr_price) t1
where taritem.tr_rowid=t1.tr_rowid and t1.rn=1;

------解决方案--------------------
SQL code

create table taritem
(tr_rowid int, 
 tr_item_code varchar(10), 
 tr_item_desc varchar(15))

insert into taritem
select 1000, 'xy100323', '阿莫西林' union all
select 1001, 'xy200365', '葡糖糖'

create table tr_price
(tp_rowid varchar(10), tr_rowid int, 
 tp_startdate date, tp_price decimal(5,1))

insert into tr_price
select '1000||1', 1000, '2001-09-08', 10 union all
select '1000||2', 1000, '2002-01-05', 11.5 union all
select '1001||1', 1001, '2003-05-08', 50 union all
select '1001||2', 1001, '2005-01-05', 50.5 union all
select '1001||3', 1001, '2008-04-25', 52


select a.tr_rowid, a.tr_item_code, a.tr_item_desc,
       b.tp_startdate,b.tp_price
from taritem a
left join
(select c.tr_rowid,max(c.tp_startdate) 'tp_startdate',
 (select tp_price from tr_price d 
  where d.tr_rowid=c.tr_rowid and d.tp_startdate=max(c.tp_startdate)) 'tp_price'
 from tr_price c group by c.tr_rowid) b
on a.tr_rowid=b.tr_rowid

/*
tr_rowid    tr_item_code tr_item_desc    tp_startdate tp_price
----------- ------------ --------------- ------------ ------------
1000        xy100323     阿莫西林            2002-01-05   11.5
1001        xy200365     葡糖糖              2008-04-25   52.0

(2 row(s) affected)
*/

------解决方案--------------------
代码如下:
SQL code
select a.tr_rowid,a.tr_item_code,a.tr_item_desc,tp_startdate,tp_price
 from (select a.tr_rowid,a.tr_item_code,a.tr_item_desc,tp_startdate,tp_price,
 ROW_NUMBER()over(partition by a.tr_rowid,a.tr_item_code,a.tr_item_desc order by tp_startdate desc) as n
 from taritem a inner join tr_price b on b.tr_rowid=a.tr_rowid) p
 where n=1

------解决方案--------------------
前面发错了;
重新发一下
SQL code

select tr_rowid,tr_item_code,tr_item_desc,tp_startdate,tp_price
 from (select a.tr_rowid,a.tr_item_code,a.tr_item_desc,tp_startdate,tp_price,
 ROW_NUMBER()over(partition by a.tr_rowid,a.tr_item_code,a.tr_item_desc order by tp_startdate desc) as n
 from taritem a inner join tr_price b on b.tr_rowid=a.tr_rowid) p
 where n=1

tr_rowid    tr_item_code tr_item_desc    tp_startdate tp_price
----------- ------------ --------------- ------------ ---------------------------------------
1000        xy100323     阿莫西林           2002-01-05   11.5
1001        xy200365     葡糖糖             2008-04-25   52.0