sql笔记

最新推荐文章于 2023-11-07 16:47:03 发布

SunWuKong_Hadoop

最新推荐文章于 2023-11-07 16:47:03 发布

阅读量1.8k

点赞数 1

分类专栏： mysql oracle linux

本文链接：https://blog.csdn.net/SunWuKong_Hadoop/article/details/73166451

版权

oracle 同时被 3 个专栏收录

39 篇文章 0 订阅

订阅专栏

linux

23 篇文章 0 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

1.将某一字段中，英文括号改为中文括号：

update 表 set 字段= replace(replace(字段,'(', '（'), ')', '）')

参照asc码：http://ascii.911cha.com/?year=tab

2.将表1与表2做匹配，匹配上的就将表2的一个字段替换到表1的某个字段。

update em_video_camera a 
set camera_remark=(select remark from fhc_test b where a.camera_code=b.camera_code),
      camera_posdes=concat(camera_code,camera_name) 
where camera_code in(select camera_code from fhc_test ) 
and camera_code not in
(
select b.camera_code 
from em_video_camera a,fhc_test b 
where a.camera_code=b.camera_code 
group by b.camera_code 
having  count(*)>1
);

原始语句：

update em_video_camera a set camera_remark=(select remark from fhc_test b where a.camera_code=b.camera_code),
camera_posdes=concat(camera_code,camera_name) where camera_code in(select camera_code from fhc_test ) and camera_code not in(
select b.camera_code from em_video_camera a,fhc_test b where a.camera_code=b.camera_code group by b.camera_code having  count(*)>1)

3.left join

select a.create_time,a.remark,c.`name` from ncc_investigation_data as a 
LEFT JOIN ncc_measured_point as b on a.point_id=b.id
LEFT JOIN ncc_measured_type as c on b.type_id=c.id
where data_source=2 and create_time='2017/5/19'

4-1.excel中找到重复的数据，并删除对应的一行数据

方法：选择一列 -->删除重复项 -->扩展选定区域 -->取消全选，选一列 -->确定

4-2.excel中快速对比两列或两组数据的相同项和不同项

=MATCH(A1,B:B,)

5.取出字符串中的数字

①.创建函数，调用函数

create function dbo.F_Get_Number (@S varchar(100))
returns int  www.2cto.com  
AS 
begin
    while PATINDEX('%[^0-9]%',@S)>0
          begin
                set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
          end
    return cast(@S as int)
end

测试：

select dbo.F_Get_Number ('chuangjiang2ada33核心价值观adc')

ps：没有试验成功！

②.

select substring(所查询字符串,patindex('%[^0-9][0-9]%',所查询字符串)+1,
patindex('%[0-9][^0-9]%',所查询字符串)-patindex('%[^0-9][0-9]%',所查询字符串))

ps:这个只能查询第一次在字符串出现的数字

6.EA反向工程导入方法

7.表，注释，字段，类型

7-1.(取得表的字段，字段类型，注释)

show full COLUMNS from 表名;

7-2.取出数据库中表名及注释

Select table_name 表名,TABLE_COMMENT表注释from

INFORMATION_SCHEMA.TABLES

Where table_schema = 'kmjkioc_theme' ##数据库名

AND table_name LIKE 'ncc_index'##表名;

7-3.取出数据库中表的字段及注释

Select COLUMN_NAME 列名, DATA_TYPE字段类型, COLUMN_COMMENT字段注释

from INFORMATION_SCHEMA.COLUMNS

Where table_name = 'ncc_index'##表名

AND table_schema = 'kmjkioc_theme'##数据库名

AND column_name LIKE 'id'##字段名

8.sql查询表中列、字段重复值

SELECT * FROM T_VALUE A
WHERE
(
A .ENTERPRICE_ID,
A .VALUE_DATE,
A .INDEX_ID,
A ."VALUE"
) IN (
SELECT
ENTERPRICE_ID,
VALUE_DATE,
INDEX_ID,
"VALUE"
FROM
T_VALUE
GROUP BY
ENTERPRICE_ID,
VALUE_DATE,
INDEX_ID,
"VALUE"
HAVING
COUNT (*) > 1
)

9.查询数据格式

数字： SELECT * FROM 表名 WHERE 字段名 not LIKE '%[^0-9]%';

非数字：SELECT * FROM 表名 WHERE 字段名 LIKE '%[^0-9]%';

10.检验统一社会信用代码

select CASE
         WHEN regexp_like('TYSHXYDM',
                          '[0-9A-HJ-NPQRTUWXY]{2}\d{6}[0-9A-HJ-NPQRTUWXY]{10}') and
              length(TYSHXYDM) = 18 THEN
          '1'
         ELSE
          '0'
       END AS output
  FROM FRJC_JBXX;

11.检验组织机构代码

CREATE OR REPLACE FUNCTION jgid_verify2(organizationCode VARCHAR2)  
/*  
    功能：验证组织机构代码，成功返回1，失败返回0  
    organizationCode：要验证的组织机构代码  
    相关资料：  
    http://baike.baidu.com/view/238601.htm  
  */  
 RETURN NUMBER AS  
  codeSum NUMBER(10) := 0;  
  code    VARCHAR(100);  
  code_9  varchar(1);  
  C9      NUMBER(2);  
  /*字符与字符的值，每个字符后两位为该字符的字符数值*/  
  Ci CHAR(250) := '000101202303404505606707808909A10B11C12D13E14F15G16H17I18J19K20L21M22N23O24P25Q26R27S28T29U30V31W32X33Y34Z35';  
  /*前8位字符的加权因子*/  
  type v_ar is varray(10) of number;  
  Wi v_ar := v_ar(3, 7, 9, 10, 5, 8, 4, 2);  
BEGIN  
  /*判断是否为null*/  
  IF (organizationCode is NULL) THEN  
    BEGIN  
      RETURN 0;  
    END;  
  END IF;  
  
  code := RTRIM(LTRIM(REPLACE(organizationCode, '-', ''))); /*把-,前后空格去掉*/  
  
  /*验证长度是否正确*/  
  /*验证机构代码是由数字和大写字母组成*/  
  IF (LENGTH(code) != 9 or NOT REGEXP_LIKE(code, '^[A-Z0-9]+$')) THEN  
    BEGIN  
      RETURN 0;  
    END;  
  END IF;  
  
  /*前8位字符的字符数值分别乘于该位的加权因子，然后求和*/  
  for i in 1 .. Wi.count loop  
    codeSum := codeSum +  
               to_Number(substr(Ci, INSTR(Ci, substr(code, i, 1)) + 1, 2)) *  
               Wi(i);  
  end loop;  
  
  /* 计算校验码C9*/  
  C9     := 11 - (codeSum MOD 11);  
  code_9 := substr(code, 9, 1);  
  
  /*验证校验码C9*/  
  /*当C9的值为10时，校验码应是拉丁字母X */  
  /*当C9的值为11时校验码应是0*/  
  /*验证第9位是否等于计算出的校验结果*/  
  IF ((C9 = 10 and code_9 = 'X') or (C9 = 11 and code_9 = '0') or  
     (code_9 = to_char(C9))) THEN  
    BEGIN  
      return 1;  
    END;  
  END IF;  
  
  RETURN 0;  
EXCEPTION  
  WHEN OTHERS THEN  
    raise;  
END;

12.多表关联

select a.dept,a.id,a.name,a.job,c.org_struct_name from pbp_cpc_info a
RIGHT join pbp_party_info b
on a.dept = b.dept_id
RIGHT join pbp_party_org c
on  a.dept=c.party_org_id
and a.job in (505,506) ORDER BY a.dept,a.job;

13.简单查询:

例子1：

①

select * from pbp_assess_index where `year`=2016 and pcode in ('A000','A001','A002','B000','B001',
'B002','C000','C001','C002','D000','D001','D002','E000','E001','E002','G000','G001','G002')
ORDER BY PCODE,ID ASC;

②

错误写法：

DELETE from pbp_cpc_info a where (name in (select name from pbp_cpc_info as b 
where a.id <> b.id
and a.job=b.job 
and a.dept=b.dept
and a.birthday=b.birthday
and a.dept in (053112304000,053112304066,053112304067,053112304065)
and trade))

正确写法：

建表，删除数据，删除临时表，验证。

create table tmp as (select *  from pbp_cpc_info as a  where (name in (select name from pbp_cpc_info as b 
where a.id <> b.id
and a.job=b.job 
and a.dept=b.dept
and a.birthday=b.birthday
and a.dept in (053112304000,053112304066,053112304067,053112304065)
and trade)) order by name,trade);

DELETE from pbp_cpc_info where id in (SELECT id from tmp);

drop TABLE tmp;
select *  from pbp_cpc_info as a  where (name in (select name from pbp_cpc_info as b 
where a.id <> b.id
and a.job=b.job 
and a.dept=b.dept
and a.birthday=b.birthday
and a.dept in (053112304000,053112304066,053112304067,053112304065)
and trade)) order by name,trade;

例子2：

create table tmp_1 as
( select * from pbp_party_org where party_org_id in
(
(select party_org_id from pbp_party_org where father_id='053112302000')
union 
(select party_org_id from pbp_party_org where father_id in 
(select party_org_id from pbp_party_org where father_id='053112302000'))
union 
(select party_org_id from pbp_party_org where id='8571ca8c-818d-11e7-bb31-be2e44b06b34')
)
);

select *  from pbp_cpc_info as a  where (name in (select name from pbp_cpc_info as b   
where a.id <> b.id  
and a.job=b.job   
and a.dept=b.dept  
and a.birthday=b.birthday  
and a.dept in 
(select party_org_id from tmp_1)  
and trade)) order by name,trade,cp_cost_level;

create table tmp_2 as (
select *  from pbp_cpc_info as a where name in (select name from pbp_cpc_info as b   
where a.id <> b.id  
and a.job=b.job   
and a.dept=b.dept  
and a.birthday=b.birthday  
and a.dept in 
(select party_org_id from tmp_1)) 
and cp_cost_level is null 
order by name,trade,cp_cost_level);

DELETE from pbp_cpc_info where id in (SELECT id from tmp_2);

14.mysql中创建视图，表中使用union报错，解决办法。

加了括号，报错：

create table tmp_1 as
(
(select * from pbp_party_org where father_id='053112302000')
union 
(select * from pbp_party_org where father_id in 
(select party_org_id from pbp_party_org where father_id='053112302000'))
union 
(select * from pbp_party_org where id='8571ca8c-818d-11e7-bb31-be2e44b06b34')
);

去掉括号：

create table tmp_1 as
(select * from pbp_party_org where father_id='053112302000')
union 
(select * from pbp_party_org where father_id in 
(select party_org_id from pbp_party_org where father_id='053112302000'))
union 
(select * from pbp_party_org where id='8571ca8c-818d-11e7-bb31-be2e44b06b34')
;

15,.excel中vlookup函数，如果遇到重复项，只会匹配最先遇到的那个，例如：

安永	1	安永	1
毕世彩	2	毕世彩	2
蔡思文	3	蔡思文	3
曹伟	4	曹伟	4
陈奎廖	5	陈奎廖	5
陈启良	6	陈启良	6
楚禹斌	7	楚禹斌	7
陈奎廖	5	陈奎廖	8
陈启良	6	陈启良	9

16.excel中归类重复项，解决办法：

先“高亮重复项”，然后进行“筛选”

17.mysql删除某一列数据中的tab键符、空格

update pbp_cpc_info set name=replace(name,'　','');

update pbp_cpc_info set name=replace(name,' ','');

18.简单查询

SELECT COUNT(party_org_id) a,party_org_id  from pbp_party_org GROUP BY party_org_id ORDER BY party_org_id,a desc

select * from pbp_party_org
where party_org_id in (053112304121,053112304003
)

19.excel中查找上下两行完全相同的数据

=IF(AND(D20=D21,E20=E21,F20=F21),"重","")

例子：

20.乱七八糟的触发器报错：

ORA-04098: trigger 'KMJK.TRIG_LT_INSERT_copy' is invalid and failed re-validation

declare
  num int;
  ent_id varchar2(64);
BEGIN
  select count(*) into num from KMJK.T_ENTERPRICE where enterprice_id = :new.ENTERPRICE_ID;
  if(num=1) then
    KMJK.P_SDE_ADD_ENT(:NEW.ENTERPRICE_ID);
  end if;

21.晚上对象给上了一课，关于join的：

left join 等的用法，可以在网上百度下，比如说这个图解join：https://coolshell.cn/articles/3463.html

其中圆饼只能是帮助理解，我就混淆了，一开始脑子里理解的是，查出的数据就是个左表全部数据或右表全部数据（傻子！）

现在知道了，原来查出的数据是以左表为主，右表中不包含左表相同这个字段（on后面接的，例如a.id=b.id）的以null代替，包含相同这个字段的数据的，后续的字段数据也查出来，O(∩_∩)O，说的好不如画的好，详看上面网址。这就是join的强大所在，在hive也好，oracle中也好，多用left join，right join，会节省很多的运行资源，提高执行效率，

22.oracle中sql优化，ps：好的博客就应该分享出来

原文地址：点击打开链接

笔记：

1.is null 与 is not null 使用时不会调用索引

2.通配符 % 放在后

3.不等于用 ‘ <> ’

4.order by后面跟字段，尽量此字段为索引字段

5.选最有效率的表名顺序:两表小表先，三表在中间

6.尽量不用：‘ * ’

7.减少访问数据库的次数，把分散的sql整合成一条

8.truncate在删除全表时比较快，不像delete时需要commit

9.多使用commit来释放资源

10.避免使用having操作，这个操作只会在检索出所有记录后才会对结果集进行过滤，这个处理需要排序，总结等操作。on、where、having从左至右依次执行，on是先把不符合条件的记录过滤后再执行统计，where是过滤数据后再执行sum。

11.减少对表的查询，换句话说：多用left join，right join 等

12.经常使用内置函数：

参考博客：点击打开链接

13.别名

14.索引

15.巧妙使用exists替换distinct或in（待研究······）

16.避免在索引上使用计算

例如：

低效：SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效：SELECT … FROM DEPT WHERE SAL > 25000/12;

17.用>=代替>

18.union替换or（适用于索引列）

正常的无索引的列，用or来的效率更高一些

19.优化group by ，将一些数据提前过滤掉。

23.查看权限，赋予权限

查看当前用户的权限：select * from user_sys_privs;

查看当前用户被授予的角色： select * from user_role_privs;

查看所有用户：select * from dba_users;
24.行列转换

select a.qhmc as '社区', 
sum(IF(a.XB=2,a.sl,0)) as '男', 
sum(IF(a.XB=1,a.sl,0)) as '女' 
from (select qhmc,xb,count(1) sl from rk_jc_rkxx GROUP BY qhmc,xb) a
group by a.qhmc

25.linux获取当前年月日时分秒

perl -e

"print sprintf '%04d-%02d-%02d %02d:%02d:%02d',

(localtime(time()+3300))[5]+1900,

(localtime(time()+3300))[4]+1,

(localtime(time()+3300))[3],

(localtime(time()+3300))[2],

(localtime(time()+3300))[1],

(localtime(time()+3300))[0]"

26.left join 后,左表怎样合并或者去掉重复记录

原文地址：http://blog.csdn.net/txqd1989/article/details/53760860

在使用left join 后会发现如果右表数量有重复的话，查询出来的数量大于左表的数量，

如表一 test1

ID	NAME	SEX	AGE
1	1	1	1
2	2	2	1
3	3	3	1

表二 test2

ID	NAME	SEX	KJXYH
1	1	1	kjxy-物采中心-20130001
2	1	1	kjxy-物采中心-20130003
3	1	1	kjxy-物采中心-20130002

普通的left join

Select t1.* ,test2.kjxyh From test1 t1 Left Join test2 On t1.Name = test2.Name And t1.sex =test2.sex

查询出来的结果：

ID	NAME	SEX	AGE	KJXYH
1	1	1	1	kjxy-物采中心-20130001
1	1	1	1	kjxy-物采中心-20130003
1	1	1	1	kjxy-物采中心-20130002
2	2	2	1
3	3	3	1

查询结果为五条数据，不是我们想要的结果，下面看看如果我用这种方式查询的结果

select test1.*,t2.kjxyh from test1 left join(
Select * From
(
 select a.*,row_number() over(partition by Name,sex order by id) r
 from test2 a
 ) where r = 1 ) t2
 on  test1.Name = t2.Name And  test1.sex = t2.sex

ID	NAME	SEX	AGE	KJXYH
1	1	1	1	kjxy-物采中心-20130001
2	2	2	1
3	3	3	1

会发现数据如只有三条了。按照排序获取到了三条，以左表为基表，然后从test2中按照指定的排序获取到了test2中的kjxyh，但是有时候需要展示所有的kjxyh该怎么办呢，看看下面这条sql语句：

 select test1.*,b.kj from test1, 
 ( select test1.id,wm_concat(test2.kjxyh) kj from test1
 left join test2 on test1.name=test2.name and test1.sex=
 test2.sex group by test1.name,test1.sex,test1.id) b
 where test1.id in(b.id)

查询结果：

ID	NAME	SEX	AGE	KJ
1	1	1	1	kjxy-物采中心-20130001,kjxy-物采中心-20130003,kjxy-物采中心-20130002
2	2	2	1
3	3	3	1

27.三个数据库中两表关联进行更新

mysql：update yz4202_jmjbxxb a,rk_ly_jbxx b set a.xl=b.xl  where a.gmsfhm=b.gmsfzh;
ms sql sever：update yz4202_jmjbxxb a set gmsfhm = b.gmsfzh FROM a,rk_ly_jbxx b where a.gmsfhm=b.gmsfzh;
oracle：update yz4202_jmjbxxb a set (gmsfhm) = (SELECT gmsfzh from rk_ly_jbxx b where a.gmsfhm=b.gmsfzh);

27.mysql中截取字符串汉字，数字

drop FUNCTION GetNum;
CREATE FUNCTION GetNum (Varstring varchar(50))
RETURNS varchar(30)
BEGIN
DECLARE v_length INT DEFAULT 0;
DECLARE v_Tmp varchar(50) default '';
set v_length=CHAR_LENGTH(Varstring);
WHILE v_length > 0 DO
IF ((ASCII(mid(Varstring,v_length,1))>47 and ASCII(mid(Varstring,v_length,1))<58 )or ASCII(mid(Varstring,v_length,1))=46) THEN
set v_Tmp=concat(v_Tmp,mid(Varstring,v_length,1));
END IF;
SET v_length = v_length - 1;
END WHILE;
RETURN REVERSE(v_Tmp);
END;

select BYLJWCS,GetNum(BYLJWCS),SUBSTRING_INDEX(BYLJWCS,GetNum(BYLJWCS),-1)  from yz0800_mbzrswcqkb;

28，计算环比

select t1.TJYF, t1.RJYD,t1.TJNF,
convert(((t1.RJYD-t2.RJYD)/t2.RJYD),decimal(10,2)) as TB
from ind_nhcc_fyzzyrjyd t1 
left join 
ind_nhcc_fyzzyrjyd t2 
on t1.TJYF-1=t2.TJYF and t1.TJNF = t2.tjnf;

29.获取当前日期前6个月内的数据，并统计车辆进出的数量（oracle）

select COMMUNITYID,COMMUNITYNAME,GATETYPE,count(GATETYPE) as ZSL from CLTXJLLQ m 
where to_date(m.INOUTTIME,'yyyy-mm-dd hh24:mi:ss') between sysdate-interval'6'month and sysdate 
GROUP BY COMMUNITYID,COMMUNITYNAME,GATETYPE ORDER BY COMMUNITYID;

30.统计近一周，近七天数据

求上一周的数据
Select * From tfjl a Where trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))>=trunc(Sysdate，'d')
AND trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))<= Next_day(trunc(sysdate,'d'),7);

求当前日期前七天的数据
Select * From tfjl a Where trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))<=trunc(Sysdate) 
and trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))>= trunc(sysdate-7);

31.查找、删除多个字段的重复数据，并保留一条

删除多字段重复数据
DELETE FROM TFJL_COPY a
WHERE (a.DEVICECODE, a.THROWTIME,a.GARBAGETYPE,a.WEIGHT) IN 
(SELECT DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT FROM TFJL_COPY GROUP BY DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT HAVING COUNT(*) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID) FROM TFJL_COPY GROUP BY DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT HAVING COUNT(*) > 1);

查找多字段重复数据
SELECT * FROM TFJL_COPY a WHERE (a.DEVICECODE, a.THROWTIME,a.GARBAGETYPE,a.WEIGHT) IN (SELECT DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT
FROM TFJL_COPY GROUP BY DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT HAVING COUNT(*) > 1);

32.oracle sql 中利用regexp_substr 函数获取字符串中的数字（小数部分）

select regexp_substr('12.12元/小时', '\d*(\d*\.\d*)?')  from dual;

33.oracle查询表注释，并按照in中的数据进行排序输出

select * from user_col_comments where table_name in 
('SJMACDZDYGXB','ZXYHXX','AQPJJGMDJLLFS','FMKSSCQYXX','WXHXPQYXX') 
order by "DECODE"
(table_name,'SJMACDZDYGXB',1,'ZXYHXX',2,'AQPJJGMDJLLFS',3,'FMKSSCQYXX',4,'WXHXPQYXX',5);

34.linux搬过来了

1.hadoop fs -du -s -h  [url]  查看目录下文件的总大小

结果：190.9 G  381.8 G  /user/hive/warehouse/ty_mrtrace.db/mr_original_l/mr_date=2017-01-21

理解：

第一列标示该目录下总文件大小

第二列标示该目录下所有文件在集群上的总存储大小，和副本数相关，我的副本数是2 ，所以第二列的是第一列的两倍 （第二列内容=文件大小*副本数）

第三列标示你查询的目录

hadoop fs -du

hadoop fs -dus 可指定某一个文件，显示其大小

hadoop fs -

2.将impala中文件整到本地系统

impala-shell -i slave02 -f mlte_s1_mmelog_f2.sql -B --output_delimiter=',' -o mlte_s1_mmelog_f2.csv >> import_log.log 2>&1 &

mlte_s1_mmelog_f2.sql里面内容：例一：select * from xdr.mlte_s1_mmelog_f2 where day='20170208' and minute='2330' limit 1000

例二：select province_id,city_id,rantype,nvl(service_type,0),phone,lac,ci,imei,nvl(traffic_style,0),starttime,endtime,duration,up_traffic,down_traffic,sum_traffic,nvl(net_type,0),client_ip,dest_ip,nvl(status,0),user_agent,apn,ims
i,sgsn_ip,ggsn_ip,content_type,nvl(source_port,0),nvl(dest_port,0),nvl(record_flag,0),nvl(merge_num,0),charac  from unicomidmp.s_com_gn_160118 where cast (home_province_id as string)='127' and city_id='12701'

3.2017/11/1linux服务器莫名被占满了

查看所有文件，占用内存从小到大：du -h  /  |  sort -h

查看当前文件夹文件内容，以K、M为单位显示：ls -lh

清空大文件：>cation.out

35.替换字段中的中文，或者只提取字段中的数字进行计算

select REGEXP_REPLACE('加33','[[:alpha:]]','') from dual;
select regexp_replace('23456,+加jia3-00=.,45','[^0-9]') from dual;

36.hive替换字符串

select distinct substr(KKBH,1,20) as KKBH,KKMC,
substr(KKMC,1,instr(KKMC,'路')-0) as KKMC2 from lqioc_ioc_ods.KKZDB where KKMC like '%路%';

37.hive求前一天的数据，求case when then else end

select regexp_replace(e.KKMC,'卡口','') as KKMC3,e.sd,
case
when e.sd>=200 then '严重拥堵'
when e.sd<200 and e.sd>=150 then '一般拥堵'
when e.sd<150 and e.sd>100 then '拥堵'
else '通畅'
end as ydqk
from 
(select d.KKMC,d.sd,row_number()over(order by d.sd desc) as sd2 from
(select  c.KKMC,c.sd,row_number()over(partition by c.KKMC order by c.sd desc) as px from 
(select
a.START_TIME,a.CHECKPOINT_ID,round(a.PASS_COUNT/60,0) as sd,b.KKMC
from 
(select STATS_TYPE,START_TIME,substr(CHECKPOINT_ID,1,20) as CHECKPOINT_ID,PASS_COUNT
from lqioc_ioc_ods.LLTJ where STATS_TYPE=0 and HOURS=24 and substr(START_TIME,1,10)=substr(FROM_UNIXTIME(UNIX_TIMESTAMP()-86400,'yyyy-MM-dd'),1,10)) a
left join 
(select distinct substr(KKBH,1,20) as KKBH,KKMC from lqioc_ioc_ods.KKZDB where KKMC like '%路%') b 
on a.CHECKPOINT_ID=b.KKBH
where b.KKMC is not null) c)d where d.px=1) e 
where e.sd2<6;

38.hive时间函数

select date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),7); --本周1--
select date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),14); --上周1--
select date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),0); --下周1--
select date_add(next_day(to_date(CURRENT_TIMESTAMP),'MO'),7); --下下周1--
select trunc(add_months(to_date(CURRENT_TIMESTAMP),-1),'MM'); --上月1号--
select trunc(to_date(CURRENT_TIMESTAMP),'MM'); --本月1号--
select trunc(add_months(to_date(CURRENT_TIMESTAMP),1),'MM'); --下月1号--

39.hive时间戳，字符串，日期转换

从1970-01-01 00:00:00 UTC到指定时间的秒数。
总结：时间戳到日期时间，日期时间到时间戳，日期时间到日期。

获取时间戳：select distinct unix_timestamp() from test_date;

时间戳>>>>日期：select distinct  from_unixtime(1441565203,'yyyy/MM/dd HH:mm:ss') from test_date; 
2015/09/07 02:46:43

日期时间>>>>>>时间戳：默认格式为“yyyy-MM-dd HH:mm:ss“：
select distinct unix_timestamp('2015-09-07 02:46:43') from test_date;
1441565203

指定日期时间>>>>>>时间戳: 
select distinct unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from test_date;   
1323234063
 
日期时间>>>>>>日期: 
select distinct to_date('2011-12-08 10:03:01') from test_date;
结果：2011-12-08

日期时间>>>>>>年/月/日/时/分/秒:   year(string date)，month(),day(),hour(),minute(),second()。
select distinct year('2011-12-08 10:03:01') from test_date;
2011
select distinct second('2011-12-08 10:03:01') from test_date;
1

40.取逗号前第一位的字符串

select (string_to_array('休闲，娱乐，运动，玩耍','，'))[1];
select split_part('休闲，娱乐，运动，玩耍','，',2);

41.postgresql求时间差

select date_part('day',NOW()::timestamp-'2019-01-01 01:00:00'::timestamp) as time_count;

42.hive替换非法字符

select regexp_replace(lower('$%^\&&*!(1、2-（）J\JJ暗示，。.,'),
'[^0-9a-zA-Z\\u4e00-\\u9fa5\\(\\)\\-\\（\\）\\、]','');

43.将中文按照顺序排列

order by (t1.zjmc='桂城街道',t1.zjmc='九江镇',
t1.zjmc='西樵镇',t1.zjmc='丹灶镇',t1.zjmc='狮山镇',
t1.zjmc='大沥镇',t1.zjmc='里水镇') desc

44.excel中获取下划线后面的字符串

=MID(C2,FIND(CHAR(13),SUBSTITUTE(C2,"_",CHAR(13),LEN(C2)-LEN(SUBSTITUTE(C2,"_",""))))+1,LEN(C2))

45.mysql中提取字符串中的201开头的6或8位数字

select cast(substr(字段,instr(字段,'201'),8) as unsigned) from 表名;

源数据：结果数据：

SunWuKong_Hadoop

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
sql笔记

1.将某一字段中，英文括号改为中文括号：update 表 set 字段= replace(replace(字段,'(', '（'), ')', '）')参照asc码：http://ascii.911cha.com/?year=tab2.将表1与表2做匹配，匹配上的就将表2的一个字段替换到表1的某个字段。updateem_video_cameraasetcamera_r...
复制链接

扫一扫

专栏目录