Shulou(Shulou.com)06/01 Report--

Basic syntax of wm_concat

select proj_id,max(scale_valus) as scale_valus

from(select a.proj_id,

wm_concat(a.scale_valu) over(partition by a.proj_id order by a.ym) as sccale_valus

from s_proj_ees_scale a

where a.ym between '201601' and '201603'

)

group by proj_id;

2.case when statement (row/column conversion)

case when a.course='' then a.score end

case when a.course in('',''...) then a.score end

3. decode function

decode(divisor, 0, display 1, normal expression other than zero)

4. Nvl function

NVL(String 1,replace_with); if String 1 is NULL, NVL returns the value of replace_with, otherwise it returns the original value (its own value), just like ISNULL(String 1,replace_with) in SQLserver.

5. Substr function

Substr ('Hello World', 2,4)//returns the result 'ello', noting that starting with 1 and starting with 0 means starting with the first character.

6. ROUND AND TRUNC

ROUND(45.926,2)=45.93

TRUNC(45.926,2)=45.92

7. Common SQL statements

Insert into tablename() values();

update tablename a set a.sal=4000 where a.ename='';

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.