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

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

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.