qq_18453581的博客

space(n:int): 生成n个空格,使用split分割成数组,然后使用posexplode展开为每行为pos,val的表。另附stack(col_num,...)函数用于行转列,例如生成2行数据。需求:生成2022-01到当前月份的所有日期生成,后续填充数据。6(字段参数)/3(列)=2(行)

开窗函数，分组排序rank() over(partition by [field] order by [field] [asc/desc]) --同排名不连续 1 1 3row_number() over(partition by [field] order by [field] [asc/desc]) --不同排名 1 2 3dense () over(partition by [field] order by [field] [asc/desc]) .

学习使用基本用法DELIMITER $$create PROCEDURE get_delivery(IN p_id VARCHAR(32))BEGINSELECT order_id,count(0)=sum(is_delivery) as delivery into @order_id, @is_delivery from user_order_goods where id=p_id;update user_order set `status`=(select @is_delivery) WH

事务锁,未提交,未设置事务锁超时select * from information_schema.innodb_trxKILL trx_mysql_thread_id//trx_mysql_thread_id:具体一条记录的值解决的办法有两个：第一：innodb_lock_wait_timeout 锁定等待时间改大my.ini文件：#innodb_lock_wait_ti...

SELECT A0, A1, A2, A3, ''排名'', A4, ''排名1'', A5, ''排名2'' FROM {0} WHERE bbh = {1} LIMIT 1 UNION SELECT * from ( SELECT A0, A1, A2, A3, A3R, A4, A4R, A5, A5R ...

复杂sql涉及:行转列,排名,差值,排名变化,left joinSELECT '排名', A0, CONCAT( '收听份额(%)', A1 ), CONCAT( '收听份额(%)', A2 ), '收听份额(%)增减值', '排名变化', concat( '收听率(%)', A1 ), concat( '收听率(%)', A2 ), '收听率(%)增减值' FROM...

SELECT A0, A1, A2, A3, "排名", A4, "排名", A5, "排名" FROM Z_20190610102908864 WHERE bbh = '三月' LIMIT 1 UNION SELECT * from ( SELECT A0, A1, A2, A3, A3R, A4, A4R, ...

最直接用dos命令mysql -u用户名 -p密码 数据库 -P端口 -h主机 --local-infile=1 -e "LOAD DATA LOCAL INFILE 'c:/test.txt' INTO TABLE 表名 FIELDS TERMINATED BY '\t' ENCLOSED BY '\t';"jdbc:mysql://ip:port/数据库名称?useUnico...

安装msyql,启动服务,修改/etc/my.cnf 中sql_mode=NO_ENGINE_SUBSTITUTION(其他略)[root@localhost mysql]# mysql -uroot -pEnter password: (密码不显示)Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQ...

具体配置请参考https://blog.youkuaiyun.com/u010916338/article/details/81367551大同小异,重点是tnsnames.ora的ORADATA这个名称,自定义的会连接不上,不知道为什么,使用ORADATA就可以了ORADATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (P...

UPDATE sqlite_sequence SET seq = 0 WHERE name = 'TableName';

pragma table_info([table_name]);

CREATE TRIGGER allLine2 AFTER delete //分别添加 delete，update，insert；一次添加为多个未测试ON AcLine_subBEGINUPDATE AcLine SET line_length = (select sum(line_length) from AcLine_sub where AcLine_sub.pid=AcLine.id ...