常用SQL

最新推荐文章于 2024-07-02 20:06:37 发布

单人影i

最新推荐文章于 2024-07-02 20:06:37 发布

阅读量147

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/a185589690/article/details/108247426

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.查询重复条数:

select FUNDCODE,CALIBERCODE,count(*) as count from 
product_fund_classify group by FUNDCODE,CALIBERCODE having count>1;

2.删除表内重复数据

delete from product_fund_classify 
where id not in 
(select a.id from ((select min(id) id from product_fund_classify group by FUNDCODE,CALIBERCODE) a));

3.查询表内所有字段 t.xx,

select 't.',COLUMN_NAME,',' from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名';

例:select 't.',COLUMN_NAME,',' from information_schema.COLUMNS 
where table_name = 'cust_ackflow' and table_schema = 'salebatch';

4.批量修改列为大写SQL脚本

--  转大写
SELECT
    concat( 'alter table ', TABLE_NAME, ' change column ', COLUMN_NAME, '  ', UCASE( COLUMN_NAME ), '  ', COLUMN_TYPE,' ',EXTRA,' comment "',COLUMN_COMMENT,'";' ) AS '修改脚本'
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = '数据库名' and  TABLE_NAME = '表名';

例:
SELECT
    concat( 'alter table ', TABLE_NAME, ' change column ', COLUMN_NAME, '  ', UCASE( COLUMN_NAME ), '  ', COLUMN_TYPE,' ',EXTRA,' comment "',COLUMN_COMMENT,'";' ) AS '修改脚本'
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = 'saleorder' AND TABLE_NAME = 'i_order_ec';


转小写:
SELECT
    concat( 'alter table ', TABLE_NAME, ' rename to ', LCASE( TABLE_NAME ), ';' ) AS ‘修改脚本’
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = '数据库名'

insert语句 , 存在不插入, 不存在在插入

写法:
insert into tree(id,name,pid) select '101','test','10' from dual where not exists(select 1 from tree where id='101');
或者
insert when (not exists (select 1 from tree where id='101')) then into tree(id,name,pid) select '101','test','10' from dual;


示例:
insert into ia_combine_market (ID,COMBINE_CODE,INIT_DATE,COMBINE_NAME,COM_INCOME_RATIO,COM_VOLAT_RATIO,COM_MAX_RETREAT_RATIO,COM_SHARPE_RATIO,COM_NET_VALUE,COM_SUM_NET_VALUE,COM_TODAY_INCOME_RATIO,COM_SUM_INCOME_RATIO,COM_MONTH_INCOME_RATIO,COM_3MONTH_INCOME_RATIO,COM_6MONTH_INCOME_RATIO,COM_YEAR_INCOME_RATIO,COM_2YEAR_INCOME_RATIO,COM_3YEAR_INCOME_RATIO,COM_5YEAR_INCOME_RATIO,COM_BENCHMARK_RATIO,COM_SUM_BENCHMARK_RATIO,COM_NET_DATE) select  '1148e245a4e14d26af99f63de88b7e73','AIJIYU','20200923','激进投资','0E-8','0E-8','0E-8','0E-8','1.00000000','1.00000000','0E-8','0E-8','0E-8','0E-8','0E-8','0E-8','0E-8','0E-8','0E-8','0E-8','0E-8','20200922' from dual  where not exists(select 1 from ia_combine_market where COMBINE_CODE = 'AIJIYU' and INIT_DATE = '20200923') ;

删除冗余数据

-- 删除 冗余数据
delete
from IA_COMBINE_INCOME
where ID not in (
    select id
    from (
             select rn, id, TRANS_ACCOUNT, COMBINE_CODE, CUST_NO, TRADE_DATE
             from (select ROW_NUMBER() OVER(PARTITION BY TRANS_ACCOUNT,COMBINE_CODE,CUST_NO,TRADE_DATE ORDER BY COMBI_INCOME_DATE DESC) rn,id,
                          TRANS_ACCOUNT,
                          COMBINE_CODE,
                          CUST_NO,
                          TRADE_DATE
                   from IA_COMBINE_INCOME)
             where rn = 1));