目录
（2）sqlserver 将 “用 特定字符 分隔的一个字段” 拆分成多个字段，然后两个表之间数据更新
数据库操作
1.完整数据库备份：
打开sqlserver 》本地连接\其他服务器连接》数据库》某数据库 右键》任务》备份(B)...》直接点击确定。
2.数据库还原：
打开sqlserver 》本地连接\其他服务器连接》数据库 右键》附加(A)...》点击添加》选择**.bak文件。
3.新建数据库表
Navicat for MySQL
连接》点击右键》新建数据库...》
| 新建数据库 | |
| 数据库名： | 自定义 |
| 字符集： | utf8 -- UTF-8 Unicode |
| 排序规则： | utf8_general_ci |
SQL语句
1、通用SQL
（1）like 模糊查询, in
[{"互联网":["网络媒体","微博","墨迹天气","河北天气","其他"]},{"气象部门":["灾害大典","灾情普查","灾情直报","其他"]}]
**.java文件
if(StringUtil.isNotEmpty(dataSource)){
if(dataSource.equals("互联网")){
sql_dataSource = " AND 数据来源 = '互联网' ";
if(StringUtil.isNotEmpty(infoSource)){
//infoSource = infoSource.replaceAll(" +", ""); //去除字符串空格
if(infoSource.equals("其他")){
//like 模糊查询
sql_infoSource = " AND 信息来源 not like '网络媒体' and 信息来源 not like '%微博%' and 信息来源 not like '墨迹天气' and 信息来源 not like '河北天气' ";
} else{
sql_infoSource = " AND 信息来源 like '%"+ infoSource +"%' ";
}
}
}else if(dataSource.equals("气象部门")){
sql_dataSource = " AND 数据来源 = '气象部门' ";
if(StringUtil.isNotEmpty(infoSource)){
if(infoSource.equals("其他")){
//in
sql_infoSource = " AND 信息来源 not in "+ "("+"'灾害大典'"+","+"'灾情直报'"+","+"'灾情普查'"+") ";
}else{
sql_infoSource = " AND 信息来源 = '" + infoSource +"' ";
}
}
}else{
sql_dataSource = " AND 数据来源 = '" + dataSource +"' ";
if(StringUtil.isNotEmpty(infoSource)){
sql_infoSource = " AND 信息来源 = '" + infoSource +"' ";
}
}
}（2）sql拼接
--SQL1.选择字段原值
select [yaosuNames] from T_ShebeiInfo
--SQL2.将SQL1原值中|替换成','
select [yaosuNames] = REPLACE([yaosuNames], '|', ''',''') from T_ShebeiInfo
--SQL3.选择字段不为空,且在字段首尾添加'
select (''''+ REPLACE(aa.yaosuNames, '|', ''',''') +'''' ) as yaosuNames from (select yaosuNames from T_ShebeiInfo where yaosuNames is not null and datalength(yaosuNames)<>0) aa
select yaosuId,showTitle,FieldName
from T_YaosuInfo
where yaosuId in ( select (''''+ REPLACE(aa.yaosuNames, '|', ''',''') +'''' ) as yaosuNames from (select yaosuNames from T_ShebeiInfo where yaosuNames is not null and datalength(yaosuNames)<>0) aa )
-----*****************************************************---------------------------*****************************************--------------------------
--SQL1.选择字段原值
select [yaosuNames] from T_ShebeiInfo
--SQL2.将SQL1原值中|替换成','
select [yaosuNames] = REPLACE([yaosuNames], '|', ''',''') from T_ShebeiInfo
--SQL3.选择字段不为空,且在字段首尾添加'
select (''''+ REPLACE(aa.yaosuNames, '|', ''',''') +'''' ) as yaosuNames from (select yaosuNames from T_ShebeiInfo where yaosuNames is not null and datalength(yaosuNames)<>0) aa
--------------------------------------------------------------
--SQL4.
SELECT b.yaosuId, b.showTitle, b.FieldName
FROM T_YaosuInfo b
WHERE yaosuId IN (
SELECT ('''' + REPLACE(aa.yaosuNames, '|', ''',''') + '''') AS yaosuNames
FROM (
SELECT yaosuNames
FROM T_ShebeiInfo
WHERE yaosuNames IS NOT NULL
AND datalength(yaosuNames) <> 0
) aa
)
--SQL5.
SELECT yaosuId, showTitle, FieldName
FROM T_YaosuInfo
WHERE yaosuId IN (
'1005','1001','1006')
-------------------------------------------------------------
-----------------------------------------------------------
--先取要素,再取数据.查询结果为空
select b.showTitle,c.rq,c.TurangShidu,c.Guangzhaodu
from T_ShebeiInfo a,(
SELECT yaosuId, showTitle, FieldName
FROM T_YaosuInfo
WHERE yaosuId IN (
SELECT ('''' + REPLACE(aa.yaosuNames, '|', ''',''') + '''') AS yaosuNames
FROM (
SELECT yaosuNames
FROM T_ShebeiInfo
WHERE yaosuNames IS NOT NULL
AND datalength(yaosuNames) <> 0
) aa
)
) b,T_ShebeiData c
where a.shebeiId=c.shebeiId
--------------
--先取要素,再取数据.自定义要素Id,可查询结果
select b.showTitle,c.rq,c.TurangShidu,c.Guangzhaodu
from T_ShebeiInfo a,(
SELECT yaosuId, showTitle, FieldName
FROM T_YaosuInfo
WHERE yaosuId IN (
'1005','1001','1006')
) b,T_ShebeiData c
where a.shebeiId=c.shebeiId
------------------------------
--先取数据,再取要素:实时表c的多个字段名，为要素表b中表字段名称的值
（3）数据库表 存储m³
1.存储m³的字段类型，不能为varchar，需改成nvarchar
2_方式1.手动操作修改字段值：表》右键》编辑前200行(E)》m³
2_方式2.sql语句： 插入insert into 表名 (字段名) values(N'm³')
修改update 表名 set 字段名=N'm³' where 主键名=主键值
--完整示例
--新建表名tableName，字段名fieldName
create table tableName (fieldName nvarchar(10))
--插入字段值m³，N将字符改为unicode字符
insert into tableName select N'm³'
--列表查询
select * from tableName
--删除表tableName
drop table tableName 2、MySQL
（1）将mysql数据库中的一个表导入到另一个数据库表中
源数据库db1，源表source_table.sql；目标数据库db2，目标表target_table.sql
1.方法一：
登录导出到的数据库，执行
create table target_table select * from db1.source_table;
select * from db2.target_table;
2.方法二：
在cmd下执行，mysqldump -u 用户名 -p db1 source_table file=d:/source_table.sql; 输入密码。
登录db2 执行 source d:/source_table.sql;
3.方法三：
登录db1 执行 select * from source_table into outfile "d:/source_table.txt"; 导出纯数据格式。
建一张和源表结构一样的空目标表;
登录db2 执行 load data infile d:/source_table.txt into table target_table。
4.方法四：
建一个odbc连接，先导出到access中，再导出到另一个库中。
目标表没有主键，更新表主键：
update t_air_station set 修正的字段和值 where 所有文件和旧值 limit 1（2）mysql库表设计变更
# 1、更改字段名
ALTER TABLE 表名 RENAME column 旧新段 to 新字段 ; （推荐）
ALTER TABLE 表名 CHANGE column 旧新段 新字段 字段类型 ; （这种办法COMMENT会清空，不建议用）
# 2、修改字段类型
# 修改表company的字段company_name，varchar长度100、不为空、备注为单位名称
ALTER TABLE company MODIFY column company_name VARCHAR(100) NOT NULL COMMENT '单位名称';
# 修改表sys_oper_log的字段json_result，为text、 备注为返回参数
ALTER TABLE sys_oper_log MODIFY column json_result text COMMENT '返回参数';
# 3、新增字段
# tb_work_order_info表，新增字段work_order_detail，varchar长度100、可为空、默认初始值为NULL、备注为工单详情
ALTER TABLE tb_work_order_info add work_order_detail varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工单详情';
# tb_work_order_info表，新增字段remark，varchar长度50、可为空、默认初始值为NULL、备注为备注
ALTER TABLE tb_work_order_info add remark varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注';
# tb_shop表，新增一个字段remark，varchar长度100、可为空、默认初始值为NULL、备注为备注
ALTER TABLE tb_shop add shop_remark varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注';（3）mysql库表数据格式化处理
# 1、将A表某列字段，全部插入到B表某列字段
insert into t_b (id,qyyhid) SELECT id id1,id id2 from t_a;
# 2、根据表tb_xunjian_info中，字段purchase_order_url以"http://"开头，查询数据；
select count(*) from tb_xunjian_info
where shop_id=94610 and purchase_order_url like "http://%";
UPDATE tb_xunjian_info SET purchase_order_url = (REPLACE(purchase_order_url,'http://jrxjfw.enuoyun.com:80/prod-api',''))
where shop_id=94610 and purchase_order_url like "http://%";
# 3、根据表tb_xunjian_info中，字段purchase_order_url以"http://"开头，查询数据；并修改替换shop_sign_url中的字符串。
UPDATE tb_xunjian_info SET purchase_order_url = (REPLACE(purchase_order_url,'http://jrxjfw.enuoyun.com:80/prod-api',''))
where purchase_order_url like "http://%";
UPDATE tb_xunjian_info SET shop_sign_url = (REPLACE(shop_sign_url,'http://jrxjfw.enuoyun.com:80/prod-api',''))
where shop_sign_url like "http://%";# 1、必须先执行步骤（1）
# 查询用户表sys_user中字段user_id、bank_id；然后将查询结果，新增到用户银行关联表sys_user_bank
insert into sys_user_bank (user_id,bank_id) SELECT user_id ,bank_id from sys_user;
# 根据bank_id为空，删除sys_user_bank表中的数据行
delete from sys_user_bank where bank_id is null;
# 根据bank_id，查询t2表的数据字段parent_id、ancestors；然后将查询结果，更新到t1表中
UPDATE sys_user_bank t1 JOIN sys_bank t2 ON t1.bank_id = t2.bank_id SET t1.parent_id = t2.parent_id, t1.ancestors = t2.ancestors;
# 2、才能再执行步骤（2）
# 用户表sys_user，修改bankId字段值为空
update sys_user set bank_id=null; # 根据字段qysh、spbm、jfnd，两表关联查询；
select *
from dkhyw_dqjfgl t1
left JOIN dkhyw_fwf_order t2 ON (t1.qysh = t2.qysh and t1.spbm = t2.spbm and t1.jfnd = t2.jfnd)
GROUP BY t1.id
# 根据字段qysh、spbm、jfnd，两表关联查询；然后更新查询结果，t1表中的字段ifInput
UPDATE dkhyw_dqjfgl t1
left JOIN dkhyw_fwf_order t2 ON (t1.qysh = t2.qysh and t1.spbm = t2.spbm and t1.jfnd = t2.jfnd)
SET t1.ifInput ='0';（4）字段格式化处理
# 根据'-'将表字段shop_no，拆分成新的2个字段merchant_code、terminal_code
SELECT tsv.shop_no,
substring_index( tsv.shop_no, '-', 1 ) merchant_code,
tsv.shop_name,
if(LOCATE('-',tsv.shop_no)<>0,substring_index( tsv.shop_no, '-', - 1 ),'') terminal_code,
tsv.id
FROM
`tb_shop_view` tsv
（5）删除查询出来的数据
-- 根据条件，查询数据
SELECT * from
tb_work_order_info
where 1=1
and (work_order_status='0' or work_order_status='2')
and create_date BETWEEN '2022-07-01 00:00:00' and '2022-07-10 23:59:59';
-- 使用delete替换SELECT * ；将直接删除查询出来的数据
delete from
tb_work_order_info
where 1=1
and (work_order_status='0' or work_order_status='2')
and create_date BETWEEN '2022-07-01 00:00:00' and '2022-07-10 23:59:59';（6）多表使用集合处理
## 查询表1和表2的交集；即查询所有订单中，有效的订单号。（推荐使用，查询速度快）
SELECT t1.ddh ddh1,t2.ddh ddh2
FROM tb_ddh1_order t1
inner join tb_ddh2_effect t2 on t2.ddh=t1.ddh;
## 查询表1和表2的交集，和表三的差集；即查询所有有效订单中，未使用过的订单号。（不推荐使用，加上left join后查询速度慢）
SELECT t1.ddh ddh1,t2.ddh ddh2,t3.ddh ddh3
FROM tb_ddh1_order t1
inner join tb_ddh2_effect t2 on t2.ddh=t1.ddh
left join tb_ddh3_rz t3 on t3.ddh=t2.ddh
where t3.ddh is null;
3、SQLServer
（1）sqlserver 删除表中 指定字符串
源表T
"单据编号" "航班计划日期" "航班号" "起飞航站代码" "降落航站代码"
"C-026413700" "2013-2-11" "CA1231" "PEK" "XIY"
"C-026413699" "2013-2-11" "CA1231" "PEK" "XIY"
"C-026413698" "2013-2-11" "CA1231" "PEK" "XIY"
"C-026413697" "2013-2-11" "CA1231" "PEK" "XIY"
"C-026413696" "2013-2-11" "CA1231" "PEK" "XIY"
目标表T：
单据编号 航班计划日期 航班号 起飞航站代码 降落航站代码
C-026413700 2013-2-11 CA1231 PEK XIY
C-026413699 2013-2-11 CA1231 PEK XIY
C-026413698 2013-2-11 CA1231 PEK XIY
C-026413697 2013-2-11 CA1231 PEK XIY
C-026413696 2013-2-11 CA1231 PEK XIY
--如果数据库中存在 表T，则删除表T
if not object_id(N'T') is null
drop table T
Go
--新建表T
Create table T(["单据编号"] nvarchar(33),["航班计划日期"] nvarchar(31),["航班号"] nvarchar(28),["起飞航站代码"] nvarchar(25),["降落航站代码"] nvarchar(25))
SELECT * FROM dbo.T
--新增数据
Insert T
select N'"C-026413700"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
select N'"C-026413699"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
select N'"C-026413698"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
select N'"C-026413697"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"' union all
select N'"C-026413696"',N'"2013-2-11"',N'"CA1231"',N'"PEK"',N'"XIY"'
Go
SELECT * FROM dbo.T
--更新表T中数据：将数据中 双隐号 删除
UPDATE T
SET ["单据编号"] = REPLACE(["单据编号"], '"', '') ,
["航班计划日期"] = REPLACE(["航班计划日期"], '"', ''),
["航班号"] = REPLACE(["航班号"], '"', ''),
["起飞航站代码"] = REPLACE(["起飞航站代码"], '"', ''),
["降落航站代码"] = REPLACE(["降落航站代码"], '"', '')
SELECT * FROM dbo.T
--更新表T中头字段：将头字段中 双隐号 删除
EXEC sp_rename 'T.["单据编号"]','单据编号'
EXEC sp_rename 'T.["航班计划日期"]','航班计划日期'
EXEC sp_rename 'T.["航班号"]','航班号'
EXEC sp_rename 'T.["起飞航站代码"]','起飞航站代码'
EXEC sp_rename 'T.["降落航站代码"]','降落航站代码'
SELECT * FROM dbo.T
--删除表T中数据
delete from T
SELECT * FROM dbo.T执行结果：
sqlserver》单击数据库》新建查询(N)》复制SQL语句到空白处》 ！执行(X)
（2）sqlserver 将 “用 特定字符 分隔的一个字段” 拆分成多个字段，然后两个表之间数据更新
将源TXT文件sourceFile_table.txt导入数据库，生成新表dbo.sourceFile_table。新增字段lon、lat、shi、xian
源表dbo.sourceFile_table
源表dbo.GeographyInfo
SQL语句：
--删除表dbo.sourceFile_table中 双隐号
UPDATE sourceFile_table
SET [s_id] = REPLACE([s_id],'"','') ,
[s_lon_lat] = REPLACE([s_lon_lat],'"','') ,
[s_shi_xian] = REPLACE([s_shi_xian],'"','')
SELECT * FROM sourceFile_table
--查询表dbo.sourceFile_table：将逗号分隔的一个字段拆分成多个字段 ；将空格分隔的一个字段拆分成多个字段
SELECT TOP 1000 [s_id],
[s_lon_lat],
[s_shi_xian],
substring([s_lon_lat],1,charindex(',',[s_lon_lat])) lon,
substring([s_lon_lat],charindex(',',[s_lon_lat]) +1,30) lat,
substring(s_shi_xian,1,charindex(' ',s_shi_xian)) shi,
substring(s_shi_xian,charindex(' ',s_shi_xian) +1,30) xian
from sourceFile_table
--更新表dbo.sourceFile_table：将逗号分隔的一个字段拆分成多个字段 ；将空格分隔的一个字段拆分成多个字段
UPDATE sourceFile_table
SET lon=substring([s_lon_lat],1,charindex(',',[s_lon_lat])),
lat=substring([s_lon_lat],charindex(',',[s_lon_lat]) +1,30),
shi=substring([s_shi_xian],1,charindex(' ',[s_shi_xian])),
xian=substring([s_shi_xian],charindex(' ',[s_shi_xian]) +1,30)
SELECT * FROM dbo.sourceFile_table
--更新表dbo.sourceFile_table：将拆分后, 字段lon数据中 逗号 删除，字段shi数据中 空格 删除
UPDATE sourceFile_table
SET [lon] = REPLACE([lon],',',''),
[shi] = REPLACE([shi],' ','')
SELECT * FROM dbo.sourceFile_table
--更新表dbo.GeographyInfo：两个表之间数据更新，更新表dbo.GeographyInfo中字段shi、xian、lon、lat数据
update GeographyInfo
set GeographyInfo.shi=TS.shi,
GeographyInfo.xian=TS.xian,
GeographyInfo.lon=TS.lon,
GeographyInfo.lat=TS.lat
from GeographyInfo,sourceFile_table TS
where GeographyInfo.rerid=TS.s_id
--查询dbo.GeographyInfo：表更新后的数据，最新1000条数据，根据id降序排序
SELECT TOP 1000 [id],
[rerid],
[shi],
[xian],
[lon],
[lat]
FROM [dbo].[GeographyInfo]
order by id desc
--删除表dbo.sourceFile_table数据
delete from sourceFile_table执行结果：
sqlserver》单击数据库》新建查询(N)》复制SQL语句到空白处》 ！执行(X)
。。。
-----------------------------------------------------------------------简单示例1-----------------------------------------------------------------------
SQL语句1：
--新建表test
create table test(pp varchar(30))
go
select * from test
--新增数据
insert into test values('耐克 DS001'),('安踏 AT002'),('阿迪达斯 AD009')
go
select * from test
--查询表test：将空格分隔的一个字段拆分成多个字段
select
substring(pp,1,charindex(' ',pp))pp1,
substring(pp,charindex(' ',pp) +1,30) pp2
from test
go
--删除表test
drop table test
go
执行结果：
sqlserver》单击数据库》新建查询(N)》复制SQL语句到空白处》 ！执行(X)
SQL语句2：
--
SELECT LEFT(商品名称, CHARINDEX(' ', 商品名称 + ' ') - 1) AS 品牌 ,
STUFF(商品名称, 1, CHARINDEX(' ', 商品名称 + ' ') + 1, '') AS 商品代码
FROM ( VALUES ( '耐克 DS001'), ( '安踏 AT002'), ( '阿迪达斯 AD009') ) t ( 商品名称 );
--
select '耐克 DS001' as col1 into #Idontkonwthis
select left(col1,(select charindex(' ',col1))), substring(col1,(select charindex(' ',col1)),(select len(col1))) from #Idontkonwthis执行结果：
-----------------------------------------------------------------------简单示例2-----------------------------------------------------------------------
SQL语句：
--①横向
declare @str1 varchar(max)
set @str1='福尔摩斯,tellme,他，在哪里'
set @str1=REPLACE(@str1,',',''',''')
exec ('select '''+@str1+'''')
--②竖向
declare @str2 varchar(max)
set @str2='福尔摩斯,tellme,他，在哪里'
set @str2='select '''+replace(@str2,',',''' as col union all select ''')
--print @str
exec(@str2+'''')
--xml解法
declare @a nvarchar(max)
declare @xml xml
set @a='aa;bb;cc;dd'
set @xml=cast('<root><col val="'+replace(@a,';','" /><col val="')+'"></col></root>' as XML)
-- select @xml
select n=t.c.value('@val','varchar(255)') from @xml.nodes('/root/col') t(c)执行结果：
sqlserver》单击数据库》新建查询(N)》复制SQL语句到空白处》 ！执行(X)
4、Oracle
（1）oracle语法
-- orcale表名、字段名必须大写，并且字母开头，否则要加英文双引号，不然无法识别
-- orcale数据更新 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-- 理论支持
UPDATE DEMO_T1 t1
SET (字段一,字段二,...) = (select 字段一,字段二,... from DEMO_T2 T2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM DEMO_T2 T2 WHERE T2.FNAME = T1.FNAME);
-- 实际操作
UPDATE SELF_NG_JR_PAD_SJGZ_20220907 t1
SET t1.ISVALID=0 WHERE EXISTS
(-- 当查询出来的数据b表的pkid等于原表的pkid时，更新数据
SELECT 1 FROM
(--取序号大于1的数据出来
SELECT * FROM
(-- 将符合条件的数据查出，并打上序号id
select PKID,KHMC,row_number() over (partition by KHMC ORDER BY pkid ) as id
from SELF_NG_JR_PAD_SJGZ_20220907
WHERE SJJD='0' AND KHMC='三河市绿农园林苗木有限公司'
) a WHERE a.id>1
) b WHERE b.PKID=t1.PKID
);
-- orcale数据更新 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- orcale数据插入<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
INSERT INTO "20220907_jrsjcl" (PKID)
SELECT a.PKID pkid FROM
(-- 将符合条件的数据查出，并打上序号id
select PKID,KHMC,row_number() over (partition by KHMC ORDER BY pkid ) as id
from SELF_NG_JR_PAD_SJGZ_20220907
WHERE SJJD='0' AND KHMC='三河市绿农园林苗木有限公司'
) a WHERE a.id>1 ;
-- orcale数据插入>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>（2）分组查询
--查询条件，需与group by 后面的一致；
--根据2个条件分组查询，结果数量为1
select PKID,KHMC,COUNT(1) from SELF_NG_JR_PAD_SJGZ WHERE SJJD='0' GROUP BY PKID,KHMC;
--根据1个条件分组查询，结果数量为>1
select KHMC,COUNT(1) from SELF_NG_JR_PAD_SJGZ WHERE SJJD='0' GROUP BY KHMC ;（3）分组去重
I、第1种数据去重 ：同一客户名称KHMC，同一沟通阶段为(SJJD='0' or SJJD is null)；此类客户只保留一个最新的。
①先查询数据
--# 方式1、KHMC存在sameKhmcRowNo大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，除了最大的其他所有pkid2
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
row_number() OVER(PARTITION BY KHMC ORDER BY PKID desc) AS sameKhmcRowNo
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (SJJD='0' or SJJD is null )
) A
WHERE A.sameKhmcRowNo>1;
--# 方式2、KHMC存在sameKhmcRowNo大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，除了最大的其他所有pkid2
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC ORDER BY PKID desc) AS sameKhmcRowNo
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (SJJD='0' or SJJD is null )
) A
WHERE A.sameKhmcRowNo>1;
--# 方式3.1、KHMC存在khmcTotalCount大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，所有pkid2
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (SJJD='0' or SJJD is null )
) A
WHERE A.sameKhmcTotalCount>1;
--# 方式3.2、KHMC存在khmcTotalCount大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，除了最大的其他所有pkid2
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，所有pkid
SELECT A.pkid2 FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (SJJD='0' or SJJD is null )
) A
WHERE A.sameKhmcTotalCount>1
--补集
MINUS
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，最大的pkid2
select t.pkid2 from
(
select sj.khmc ,count(1) ,max(PKID) pkid2
from SELF_NG_JR_PAD_SJGZ sj
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (sj.SJJD='0' or sj.SJJD is null )
group by sj.khmc
having count(1)>1
)t;②查询并修改数据
--# 方式1、KHMC存在sameKhmcRowNo大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，除了最大的其他所有pkid2
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
row_number() OVER(PARTITION BY KHMC ORDER BY PKID desc) AS sameKhmcRowNo
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (SJJD='0' or SJJD is null )
and KHMC='霸州市海硕金属制品有限公司'
) A
WHERE A.sameKhmcRowNo>1;
--sql模板update N n set n.field_name='' where exists (select 1 from M m where m.id=n.id);
--根据表M查询结果，修改表N中字段值
update SELF_NG_JR_PAD_SJGZ n set n.ISVALID=0
where exists
(
select 1 from
(
-------开始
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
row_number() OVER(PARTITION BY KHMC ORDER BY PKID desc) AS sameKhmcRowNo
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
and (SJJD='0' or SJJD is null )
and KHMC='霸州市海硕金属制品有限公司'
) A
WHERE A.sameKhmcRowNo>1
-------结束
) m where m.pkid2=n.PKID
);II、第1、2种数据都去除 ：同一客户名称KHMC，存在沟通阶段为(SJJD='0' or SJJD is null)；此类客户都不保留。
①先查询数据
--# 方式3.1、KHMC存在khmcTotalCount大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）；查到的重复客户中，所有pkid2
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
) A
WHERE A.sameKhmcTotalCount>1;
--# 方式3.2、KHMC存在khmcTotalCount大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，所有pkid2
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
) A
WHERE A.sameKhmcTotalCount>1
and (A.SJJD='0' or A.SJJD is null );
--# 方式3.3、KHMC存在khmcTotalCount大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，所有khmc2
SELECT B.khmc2 FROM
(
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
) A
WHERE A.sameKhmcTotalCount>1
and (A.SJJD='0' or A.SJJD is null )
) B
group by B.khmc2;②查询并修改数据
--# 方式3.3、KHMC存在khmcTotalCount大于1，即为重复的客户
--根据数据来源KHLY为22白名单、是否有效ISVALID为有效（0无效|1有效）、沟通阶段SJJD为未沟通过；查到的重复客户中，所有khmc2
SELECT B.khmc2 FROM
(
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
) A
WHERE A.sameKhmcTotalCount>1
and (A.SJJD='0' or A.SJJD is null )
) B
group by B.khmc2;
--sql模板update N n set n.field_name='' where exists (select 1 from M m where m.id=n.id);
--根据表M查询结果，修改表N中字段值
update SELF_NG_JR_PAD_SJGZ n set n.ISVALID=0
where exists
(
select 1 from
(
-------开始
SELECT B.khmc2 FROM
(
SELECT * FROM
(
select PKID pkid2,KHMC khmc2,SJJD, KHLY,ISVALID,
COUNT(1) OVER(PARTITION BY KHMC) AS sameKhmcTotalCount
from SELF_NG_JR_PAD_SJGZ
WHERE 1=1
and KHLY='22白名单'
and ISVALID=1
) A
WHERE A.sameKhmcTotalCount>1
and (A.SJJD='0' or A.SJJD is null )
) B
group by B.khmc2
-------结束
) m where m.khmc2=n.KHMC
);5、MyBatis
（1）mybatis返回数据库自动生成主键
// 一、ShopInvoiceMapper.xml文件中
<!--实体对象ShopInvoice的字段invoiceInfoId；对应表sys_shop_invoice的主键为invoice_info_id。-->
<insert id="insertShopInvoice" parameterType="ShopInvoice" keyProperty="invoiceInfoId" useGeneratedKeys="true">
insert into sys_shop_invoice ****
</insert>
// 二、**.java文件中
// 数据库表sys_shop_invoice，新增一条数据
shopInvoiceService.insertShopInvoice(shopInvoice);
// 获取数据库表sys_shop_invoice，新增一条数据的invoiceInfoId
Long invoiceInfoId=shopInvoice.getInvoiceInfoId();（2）select in
@Column
@Schema(allowableValues = "菜单状态 1正常 2停用")
String status;
@Schema(allowableValues = "批量状态 逗号隔开的字符串")
String statuss;
@Schema(allowableValues = "批量状态 数组")
String[] statussArr;
<if test="statussArr != null">
and res.status in
<foreach collection="statussArr" item="item" index="index" open="(" separator="," close=")">
#{item,jdbcType=VARCHAR}
</foreach>
</if>
325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
