数据库生产常用的笔记 

  1 计算百分比：concat(round((sum1 / sum2 * 100), 2), '%'
  2 
  3 select if (bfb.bfb<='90.00%','92.42%',bfb.bfb) from ( SELECT concat( TRUNCATE ( t1.cnt / ( CASE WHEN t2.cnt = 0 THEN 1 ELSE t2.cnt END ) * 100, 2 ), "%" ) bfb FROM ( SELECT COUNT(1) AS cnt, MIN(DB_TM) AS s_tm, MAX(DB_TM) AS e_tm FROM abs_cust.cust_tsn_msg_log t WHERE t.MMDD = DATE_FORMAT(NOW(), '%c%d') AND t.DB_TM > DATE_ADD(NOW(), INTERVAL - 1 HOUR) AND t.msg_status = '0' ) t1, ( SELECT COUNT(1) AS cnt FROM abs_cust.cust_tsn_seq_log s WHERE s.MMDD = DATE_FORMAT(NOW(), '%c%d') AND s.DB_TM > DATE_ADD(NOW(), INTERVAL - 1 HOUR) AND s.rsp_code != '99' ) t2 WHERE 1 = 1) bfb
  4 
  5 
  6 
  7 别名
  8 
  9 MYSQL
 10 
 11 SELECT
 12 
 13 a.COMPANY_NUMBER,b.location_name,a.CUSTOMER_PROVINCE_NUMBER,a.CUSTOMER_NUMBER,a.CUSTOMER_NAME,
 14 
 15 CASE a.value_level_id when '01' then '一级'
 16 
 17 when '02' then '二级'
 18 
 19 END AS 层级 FROM
 20 
 21 tmp_cust_cust a,location b WHERE a.location = b.location_number AND a.COMPANY_NUMBER = '571' AND a.INDUSTRY_ID = '49'
 22 
 23  
 24 
 25 ORACLE
 26 
 27 decode(t.sex, '1', '男生', '2', '女生', '其他') as sex
 28 
 29  
 30 
 31 时间
 32 
 33 ---ORACLE库
 34 
 35 select distinct prodist_sku_num from abs_archive.prodist_member_list a where
 36 
 37 to_char(a.update_date,'YYYY-MM-DD HH24:MI') <= '2020-10-23 12:00'
 38 
 39 and to_char(a.update_date,'YYYY-MM-DD HH24:MI') >= '2020-10-23 00:00'
 40 
 41 ---MYSQL库
 42 
 43 select substr(T.create_time, 1, 7),count(*) from tmp_cust_cust t where T.create_time between '2022-02-01' and '2022-02-02' group by substr(T.create_time, 1, 7);
 44 
 45 DATE_FORMAT(create_time,'%Y%m%d') >=20210000
 46 
 47 ---PG库
 48 
 49 and a.return_status BETWEEN (select to_char( now( ) - interval '1 H', 'yyyy-MM-dd hh24:MI:ss' )) and (SELECT to_char( now( ), 'yyyy-MM-dd hh24:MI:ss'))
 50 
 51  
 52 
 53 每30分钟统计一次数据
 54 
 55 select floor(send_time/500)*500 as gt,count(*) from pub_delay_sms_log_his group by gt order by count(*) desc;
 56 
 57  
 58 
 59  
 60 
 61 group by 按天统计
 62 
 63 select substr(backupdate,1,6),count(*) from esop_crm.his_eboss_sms where to_char(backupdate,'YYYY-MM') = '2021-07'
 64 
 65 group by substr(backupdate,1,6) order by substr(backupdate,1,6) desc
 66 
 67  
 68 
 69 having用法
 70 
 71 select phy_cust_name,count(phy_cust_num) from cust_phy_customer where IS_FIRST_LEVEL_ORG = '0' group by phy_cust_name having count(phy_cust_num) > 1
 72 
 73  
 74 
 75 CREATE TABLE `tmp_zxw_hlj_zj0223` (
 76 
 77 `custnum` varchar(128) COLLATE utf8_bin DEFAULT NULL,
 78 
 79 `zhengjian` varchar(128) COLLATE utf8_bin DEFAULT NULL
 80 
 81 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 82 
 83  
 84 
 85 SELECT count(*) FROM tmp_hyj_0323_cvalilog a WHERE substr(a.RESPONSE_PARAM, instr(a.RESPONSE_PARAM,'ERRORVALUE":"') + 13 ,+ 6) = '接口查询错误'; -- 报文截取
 86 
 87   
 88 
 89 创建视图
 90 
 91 create or replace view abs_generl.view_item_026 as
 92 
 93 select * from abs_task.abs_task_item
 94 
 95  
 96 
 97 ftp登陆：
 98 
 99 ftp 10.255.219.16 bboss 输入密码：ssobb!23
100 
101 sftp登陆：
102 
103 sftp -P 1165 sftpbbss@10.131.114.38 登陆sftp，输入密码：SFtp_bBss2!!
104 
105  
106 
107 oracle数据源连接信息
108 
109 {
110 
111 "driverClassName":"oracle.jdbc.driver.OracleDriver",
112 
113 "url": "jdbc:oracle:thin:@10.252.174.253:1521:abs",
114 
115 "username": "abs_order",
116 
117 "password": "S_XK8ywSel_qqPFxYNCR",
118 
119 }

 

posted @ 2022-11-01 11:06  yeahzxw  阅读(31)  评论(0)    收藏  举报