常用sql汇总
-- 常见的sql json 替换查询 表头金额汇总的数据升级 --
update t_template_bill header INNER JOIN
(select sum(SUBSTRING_INDEX(REPLACE(json_values,CONCAT(SUBSTRING_INDEX(json_values,'"ud_jine":',1),'"ud_jine":"'),''),'"',1)) as sum_amount,parentid FROM t_template_bill_entry GROUP BY parentid) entry
on header.id = entry.parentid set header.bill_amount_basic = entry.sum_amount;
-- erp --
use iworker_inside;
DROP PROCEDURE IF EXISTS `add_table`;
DELIMITER $$
create procedure add_table
(
in tblName nvarchar(100),
in colInfoes nvarchar(4000)
)
begin
DECLARE CurrentDatabase VARCHAR(100);
SELECT DATABASE() INTO CurrentDatabase;
IF NOT EXISTS(select 1 from information_schema.TABLES where `TABLE_SCHEMA` = CONVERT(CurrentDatabase USING utf8) AND `table_name` = tblName) THEN
-- not EXISTS then create table
set @sql = concat('CREATE TABLE ',tblName,'(',colInfoes,') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;');
-- select @sql;
PREPARE stmtCreateSql FROM @sql;
EXECUTE stmtCreateSql;
DEALLOCATE PREPARE stmtCreateSql;
END IF;
end;$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `add_columns`;
DELIMITER $$
create procedure add_columns
(
in tblSchema nvarchar(50),
in tblName nvarchar(50),
in colInfoes nvarchar(2000)
)
begin
declare colName nvarchar(50) default '';
declare colInfo nvarchar(100) default '';
declare separaterIdx int;
declare colSQL nvarchar(1000) default CONCAT('ALTER TABLE ',tblName);
set colInfoes = trim(colInfoes);
build: LOOP
set separaterIdx = instr(colInfoes,'|');
if separaterIdx>0 Then
begin
set colInfo = left(colInfoes,separaterIdx-1);
set colInfoes = trim(substring(colInfoes,separaterIdx+1));
end;
else
begin
set colInfo = colInfoes;
end;
end if;
set colName = left(ltrim(colInfo),instr(colInfo,' '));
IF not exists(select 1 from information_schema.columns
where table_schema = tblSchema AND table_name = tblName AND column_name = colName) Then
begin
set colSQL = concat(colSQL,' ADD COLUMN ',colInfo,',');
end;
End IF;
if separaterIdx>0 then
ITERATE build;
end if;
LEAVE build;
END LOOP build;
if length(colSQL) > 16 then
set @Sql = concat(trim(TRAILING ',' from colSQL),';');
select @sql;
PREPARE stmtinsert FROM @Sql;
EXECUTE stmtinsert;
DEALLOCATE PREPARE stmtinsert;
end if;
end;$$
DELIMITER ;