My Oracle SQL summary !!!

1. ## query of telephone bill products and commissions -- SQL
SQL code
-- Select count (*)
Select t2.product _ name, t2.money, t1.fast _ price, t1.fast _ commission, t2.roll _ back
From t_commission t1, t_product_type t2, T_BUSSINESS_SAFE t3
Where 1 = 1 and t3.type _ num = t1.type _ num and t1.product _ num = t2.product _ num
-- The following parameters must be passed in dynamically.
And t3.bussiness _ id = '20180101' and t2.operators _ code = '41' and t2.area _ code = '20180101'
And t2.money in (5,100,200)
 
2. ## SQL
SQL code
Select t. *, t. rowid from t_product_type t
 
Select distinct t1.province _ code, t2.province, t3.d _ name,
T1.operators _ code, t1.roll _ back
FROM t_product_type t1, t_area t2, t_dictionary t3
Where t1.province _ code = t2.province _ code and t1.operators _ code = t3.d _ code
Order by t1.province _ code asc, t1.operators _ code asc
 
Bytes ---------------------------------------------------------------------------------------
-- Select count (*) from () If you want to query the number of result sets below, you only need to set the following SQL statement in brackets of from
Select tpt. province_code as provinceCode, max (ta. province) as proviceName,
Max (decode (tpt. operators_code, '40', 'mobile', '') as opn1,
Max (decode (tpt. operators_code, '40', '40', '') as op1,
Max (decode (tpt. operators_code, '40', tpt. roll_back, '') as rb1,
Max (decode (tpt. operators_code, '41', 'connected', '') as opn2,
Max (decode (tpt. operators_code, '41', '41', '') as op2,
Max (decode (tpt. operators_code, '41', tpt. roll_back, '') as rb2,
Max (decode (tpt. operators_code, '42', 'telecom ', '') as opn3,
Max (decode (tpt. operators_code, '42', '42', '') as op3,
Max (decode (tpt. operators_code, '42', tpt. roll_back, '') as rb3
From t_product_type tpt left join t_area ta on tpt. province_code = ta. province_code
Group by tpt. province_code
-- Having conditions can be added.
-- Having 1 = 1 and tpt. province_code = '20140901'
 
-- Update
Update t_product_type t set t. roll_back = '10' where t. province_code = '20140901'
And t. operators_code = '40'
 
3. ## text message package query statistics background-SQL
SQL code
-- Query (Fuzzy ):
Select bi. bussiness_id, bi. bussiness_name, vsc. *, dic. d_server_name from
T_bussiness_combo bc left join t_bussiness_info bi on bc. bussiness_id = bi. bussiness_id
Left join t_value_service_combo vsc on bc. combo_num = vsc. combo_num
Left join t_dictionary dic on dic. d_code = vsc. state where 1 = 1;
Bytes ----------------------------------------------------------------------------------------------------------------
-- Details:
Select t2.bussiness _ id, t3.bussiness _ name, t1.combo _ name, t1.combo _ info,
T1.combo _ duration, t1.combo _ money, dic. d_server_name from
T_value_service_combo t1, t_bussiness_combo_record t2, T_BUSSINESS_INFO t3, t_dictionary dic
Where t1.combo _ num = t2.combo _ num and t2.bussiness _ id = t3.bussiness _ id
And t2.record _ type = dic. d_code and t2.bussiness _ id = '20140901 ';
 
4. ## unilateral SQL statements-Statistics
Java code
-- Unilateral SQL statement -- statistics
Select sum (1) TOTALNUM,
Sum (decode (t. operators_code, '40',) * decode (t. state, '0',) YIDONGNUM,
Sum (decode (t. operators_code, '41',) * decode (t. state, '0',) liw.gnum,
Sum (decode (t. operators_code, '42',) * decode (t. state, '0',) DIANXINNUM,
Sum (t. money) TOTALPRICE,
Sum (decode (t. operators_code, '40', t. money, 0) * decode (t. state, '0', 1, 0) yidonuplice,
Sum (decode (t. operators_code, '41', t. money, 0) * decode (t. state, '0', 1, 0) liw.uplice,
Sum (decode (t. operators_code, '42', t. money, 0) * decode (t. state, '0', 1, 0) DIANXINPRICE
From t_phone_sale t where t. bussiness_id = '2013 ';
 
5. ## query the total number of successful moves by type + status
SQL code
-- Query the total number of successful moves by type + status
Select sum (decode (t. operators_code, '40',) * decode (t. state, '0',) yidongNum
From t_phone_sale t -- 40 is mobile

6. Sort the result set in reverse chronological order and in positive order of type_num.
SQL code
-- Reverse chronological order and forward order of type_num result set
Select * from t_commission t order by t. create_date desc, t. type_num desc
 
Select * from t_commission t order by t. create_date desc, t. type_num asc
 
7. Online recharge, business query, and additional statistics
SQL code
-- Front-end: Online recharge, business query,
-- Add statistical items (total number of businesses, total number of successful businesses, total funds, number of successful business funds, and total Commission)
-- SQL:
Select count (*) as recordTotal, nvl (sum (decode (t1.state, '0', 1, 0), 0) as successTotal,
Nvl (sum (t1.money), 0) as moneyTotal,
Nvl (sum (decode (t1.state, '0', t1.money, 0), 0) as successMoneyTotal,
Nvl (sum (decode (t1.state, '0', t1.commission, 0), 0) as commissionTotal
From T_PHONE_SALE t1 where 1 = 1 and t1.state = '0' and t1.bussiness _ id = '20160'
And t1.create _ date> = to_date ('2017-07-27 ', 'yyyy-MM-dd HH24: MI ')
And t1.create _ date <= to_date ('2017-08-26 ', 'yyyy-MM-dd HH24: MI ');
 
8. query the first 10 records in Oracle
SQL code
Select * from t_product_type where rownum <= 10

Author "Only the heart peace will be prosperity"