wm_concat(字段) 内容列顯示 變成行顯示
substr(字段,number)
trunc(字段,number)number為空截取全部小數,不四舍五入,限定小數的位數
instr(‘abc’,’a’);表示在abc中查找有沒有a這個字元。
select ceil(9.5) from dual union select floor(9.5) from dual;
結果為:9
10
--1
select year,wm_concat(amount) from test group by year
結果為:
——————————————————————
|year | --- |
|2015 | 1.1,1.2,1.3 |
|2016 | 1.1,1.2,1.3 |
——————————————————————
--2
select year,substr(wm_concat(amount),1,3) m1,substr(wm_concat(amount),-7,3) m1,substr(wm_concat(amount),-3) m1 from test group by year
————————————————————————
|year | m1 | m2 | m3 |
|2015 | 1.1 |1.2 | 1.3 |
|2016 | 1.1 |1.2 | 1.3 |
————————————————————————