表结构:
ID int -- Id a varchar --增加率 b varchar --年份 time varchar --时间条件 1d 1m 3m
sql语句:
一天的记录 select a,b from table where time=1d ---------------------------------- | Id | 年份 | 增加率 | ---------------------------------- | 1 | 2006 | 7.0 | ---------------------------------- | 2 | 2007 | 8.0 | ---------------------------------- | 3 | 2008 | 9.0 | ---------------------------------- 一月的记录 select a,b from table where time=1m ---------------------------------- | Id | 年份 | 增加率 | ---------------------------------- | 4 | 2006 | 13.0 | ---------------------------------- | 5 | 2007 | 14.0 | ---------------------------------- | 6 | 2008 | 15.0 | ---------------------------------- 三月的记录 select a,b from table where time=3m ----------------------------------- | Id | 年份 | 增加率 | ----------------------------------- | 7 | 2006 | 27.0 | ----------------------------------- | 8 | 2007 | 28.0 | ----------------------------------- | 9 | 2008 | 29.0 | -----------------------------------
能不能用一条语句实现下边的效果。 ----------------------------------------- | Id | 一天 | 一月 | 三月 | 年份 | ----------------------------------------- | 1 | 7.0 | 13.0 | 27.0 | 2006 | ----------------------------------------- | 2 | 8.0 | 14.0 | 28.0 | 2007 | ----------------------------------------- | 3 | 9.0 | 15.0 | 29.0 | 2008 | ----------------------------------------- 注释:数据库为DB2,写个sql语句。
select td.id,td1d.a as "一天",td2m.a as "一月",td3m.a as "三月", td.b as "年份" from duangh td left join (select id, b , a from duangh where time='1d') as td1d on td.b=td1d.b left join (select b, a from duangh where time='2m') as td2m on td.b=td2m.b left join (select b, a from duangh where time='3m') as td3m on td.b=td3m.b where td.id=td1d.id order by td.b asc