2023-12-18
[DB2]2个数字字段相除报错解决方案
报错:A decimal divide operation is not valid because the result would have a negative scale原因主要是分母有问题,所以一般只需转换分母即可也可以分子分母一起转换
2023-11-07
[DB2]把一张表放到另外一张表的右侧/连接到右侧
SELECT * FROM ( SELECT row_number() OVER (ORDER BY PROD_LINE_NAME) AS mynum, PROD_LINE_NAME LLINE, --产线名称 SG_SIGN LSG_SIGN
2023-11-07
[MYSQL]累计和每一天的数据互相转换
假如某个表只有累计或者只有每天的数据需要求另外一种数据数据表(假如表的后2行不是同时存在) id date monv dayv 1 2023-7-14 23 23 2 2
2023-11-07
[MYSQL]ERROR 1045 (28000)解决:
报错信息： Access denied for user 'root'@'localhost' (using password: NO/YES)解决思路：（skip-grant-tables放弃权限验证，任何密码都能登录，修改成功以后需要
2023-11-07
[DB2]查询字符串的字段UNION数字字段报错解决
SELECT 9||'' AS dfield, 1 morder FROM "SYSIBM".SYSDUMMY1 s UNION ALL SELECT '字符串' AS dfield,2 morder FROM "SYSIBM".SYSDUMMY1 s ORDER BY
2023-11-07
[DB2]添加主键及索引
-- 建立主键 ALTER TABLE GBSC00.WJGTEST2 ADD CONSTRAINT WJGTEST2_PK PRIMARY KEY (ID); ALTER TABLE GBSC00.WJGTEST2 ADD CONSTRAINT WJGTEST2_UN UNIQUE (USERS);
2023-08-01
[DB2]按照一个日期求本周及下周
SELECT TO_CHAR(TO_DATE('2023-07-01','yyyy-MM-dd')-(DAYOFWEEK_ISO(TO_DATE('2023-07-01','yyyy-MM-dd'))-1),'yyyyMMdd') A
2023-08-01
[MYSQL]求按照一个日期求本周及下周
SELECT DATE_SUB('2023-07-01',INTERVAL WEEKDAY('2023-07-01') DAY) as firstDay, DATE_ADD('2023-07-01',INTERVAL 6-WEEKDAY('2023-07
2023-08-01
[MYSQL]查询指定日期的数据，没有的话日期按照最新日期计算
SELECT * FROM ODS_ZGCB_UPJG WHERE 1 = 1 AND RIQI =IF ( (SELECT count(*)> 0 FROM ODS_ZGCB_UPJG WHERE RIQI ='202306'), '2023
2023-07-14
[MYSQL]累计和每一天的数据互相转换
数据表 id date mnum 1 2023-7-14 23 2 2023-7-15 26 3 2023-7-16 30 4 2023-7-17 50 -- 如果数据表中的date是2
2023-07-03
[DB2]更新小数位数,统一小数位数
数据库的小数位很长的情况，批量更新小数位信息 --超过4位小数的保留4位小数 UPDATE GBSC00.TCOSC02_ADS SET PROD_YIELD_INDEX_VALUE_DAY = ROUND(PROD_YIELD_INDEX_VAL
2023-07-03
[DB2]查看表的结构
SELECT TBCREATOR AS schema, TBNAME AS 表名, COLNO AS 序号, NAME AS 字段名, REMARKS AS 字段中文名, COLTYPE AS 字段类型, LE
2023-07-03
[MYSQL]查看表结构
SELECT TABLE_SCHEMA as TBDATA, TABLE_NAME as TBNAME, TRIM(COLUMN_NAME) as TBCOLUMN, UPPER(TRIM(COLUMN_NAME)) as TBCONDITION, COLUMN_TYPE as TBTYPE, if(
2023-07-03
[DB2]获取期间内所有日期
WITH DATATEMP(DATELIST) AS ( VALUES(FIRST_DAY(CURRENT DATE)) UNION ALL SELECT DATELIST + 1 DAY FROM DATATEMP WHERE DATELIST +1 DAY <= LAST_DAY(CURREN
2023-07-03
[DB2]自动更新时间,用来判断数据有没有修改
alter table TABLENAME add column COLUMNNAME timestamp not null generated always for each row on update as row change timestamp --如果要隐藏这个字段的话追
2023-07-03
[DB2]时间操作函数
--查询昨天的日期 SELECT CURRENT DATE - 1 DAYS FROM SYSIBM.sysdummy1 --2023-05-06 --日期转字符串 TO_CHAR ((SELECT CURRENT DATE FROM SYSIBM.sysdu
2023-07-03
[MYSQL]时间操作函数
--查询当前日期 SELECT NOW(); --日期格式化 SELECT DATE_FORMAT(NOW(),"%Y%m%d235959") --查询前一天 SELECT DATE_FORMAT(date_sub(curdate(),interval 1 day),"%Y
