Common sentences in oracle
1.decode function (small version of case when)
Select decode (pd.discount_id,null,'',' purchase'| | pd.product_count | | 'one,' | | pd.product_discount_rate | | 'discount') as discount
From b2b_product d right join b2b_product_hot ph on d.product_id = ph.product_id left join b2b_dictionary a
On d.RRO_MARQUE = a.CODE left join b2b_dictionaryindex b on d.RRO_BRAND = b.INDEXCODE
Left join b2b_company c on d.COMPANY_ID = c.COMPANY_ID
Left join b2b_product_discount pd on pd.product_id = d.product_id
Order by ph.sort, ph.pro_type
2.varchar sorting
Select p.code
Case
When p.state ='0' then
Decode (b.state, '1th,' 1th,'0')
When p.state ='1' then
Decode (b.state, '0mm,' 0mm,'1')
End as
From b2b_paytype_dic p, b2b_buyer_paytype b
Where p.code = b.paytype_code (+)
And (b.buyer_id =''or b.id is null)
Order by to_number (p.code)
3.case then statement
Select case when order_state= 2 then 'has been exchanged for' when order_state= 3 then 'transaction succeeded' end ORDER_STATE, sum (order_count) as cnt from t_report_order t where 1
4. The display sql of the mobile phone number for example (158 telephone number 2640)
Select substr (r. MobileMagne1) 3) | |'*'| | substr (r.mobileMagne8) as mobile from t_prize_record r
5. Give a table backup select * into xyq_college_20120301 (alias of the table) from xyq_college (table name) 6.convert intercept time select * from vas_mt_viewmt where phone = '15966605352' and convert (nvarchar (10), createtime,121) =' 2011-09-14 times 7. Color-convert the string time column, uniformly into time, and minus one update wfjs_useStats_woman set createdate=convert (varchar (10), cast (createdate as datetime)-1120) 8. Only copy sql create table b as select * from a where 11 9 of table structure. That is, copy the table structure and copy the sql create table b as select * from a 10 of the data in the table, insert multiple table data into one table, insert into target table test (field 1. Field n) (select field 1. Field n) from table union all select field 1. Field n from table
