查询语句如下:
select a.BL1,a.DCRQ,b.qhmc from XM_YS_YCXTDC_D a,sys_xzqhb b where a.sxbm in ('410201','410301','410102') and b.qhbm=a.sxbm and DCRQ >= TIMESTAMP '2006-08-01 00:00:00' and DCRQ <= TIMESTAMP '2009-08-09 23:59:59' order by DCRQ,qhmc结果为以下的格式BL1 DCRQ QHMC
8.00 2009-06-05 登封县
20.00 2009-06-05 孟县
25.00 2009-06-05 孟津县
14.00 2009-06-10 登封县
22.00 2009-06-10 孟县
35.00 2009-06-10 孟津县
20.00 2009-06-15 登封县
23.00 2009-06-15 孟县
40.00 2009-06-15 孟津县 如何转化成这样的格式 登封县 孟县 孟津县
2009-06-05 8.00 20.00 25.00
2009-06-10 14.00 22.00 35.00
2009-06-15 20.00 23.00 40.00
求教sql语句,不用数据库的特殊关键字,如oracle的rownum等。
select a.BL1,a.DCRQ,b.qhmc from XM_YS_YCXTDC_D a,sys_xzqhb b where a.sxbm in ('410201','410301','410102') and b.qhbm=a.sxbm and DCRQ >= TIMESTAMP '2006-08-01 00:00:00' and DCRQ <= TIMESTAMP '2009-08-09 23:59:59' order by DCRQ,qhmc结果为以下的格式BL1 DCRQ QHMC
8.00 2009-06-05 登封县
20.00 2009-06-05 孟县
25.00 2009-06-05 孟津县
14.00 2009-06-10 登封县
22.00 2009-06-10 孟县
35.00 2009-06-10 孟津县
20.00 2009-06-15 登封县
23.00 2009-06-15 孟县
40.00 2009-06-15 孟津县 如何转化成这样的格式 登封县 孟县 孟津县
2009-06-05 8.00 20.00 25.00
2009-06-10 14.00 22.00 35.00
2009-06-15 20.00 23.00 40.00
求教sql语句,不用数据库的特殊关键字,如oracle的rownum等。
2 to_char(max(case when qhmc = '孟县 ' then bl1 else 0 end), '99.99') as "孟县",
3 to_char(max(case when qhmc = '孟津县' then bl1 else 0 end), '99.99') as "孟津县"
4 from test
5 group by dcrq
6 order by dcrq;DCRQ 登封县 孟县 孟津县
-------------------- ------ ------ ------
2009-06-05 8.00 20.00 25.00
2009-06-10 14.00 22.00 35.00
2009-06-15 20.00 23.00 40.00
sum(decode(b.qhmc,'孟县',a.bl1)) 孟县,
sum(decode(b.qhmc,'孟津县',a.bl1)) 孟津县
from XM_YS_YCXTDC_D a,sys_xzqhb b
where a.sxbm in ('410201','410301','410102') and
b.qhbm=a.sxbm and DCRQ >= TIMESTAMP '2006-08-01 00:00:00' and
DCRQ <= TIMESTAMP '2009-08-09 23:59:59'
group by a.dcrq
order by a.dcrq
顶四楼的
支持这个,这样的竖转横的用decode和group by的配合。