达梦、oracel、mysql数据库兼容
2024-10-19 18:39:44
联合表更新sql语句：
只支持mysql、oracle，不支持达梦
update
to_pub_report a,
to_pub_rec_process b
set
a.Satisfy_ID = 1,
a.Satisfy_Content = '默认满意'
where
a.rec_id = b.rec_id
and a.Type_ID = 1
and a.State_ID = 4
and a.syn_flag = 1
and a.Satisfy_ID is null
and b.act_property_id = 101
and b.create_time < '2019-08-07 17:04:05'
支持mysql、oracel、达梦的语法：
UPDATE to_pub_report a
SET a.Satisfy_ID = 1, a.Satisfy_Content = '默认满意'
WHERE EXISTS(SELECT 1 FROM to_pub_rec_process b WHERE a.rec_id = b.rec_id
and a.Type_ID = 1
and a.State_ID = 1
and a.syn_flag = 1
and a.Satisfy_ID is not null
and b.act_property_id = 0
and b.create_time = '2019-08-07 17:04:05'
);
sum（条件）只支持mysql，
select user_id, " +
" sum(state_id > 0) as reportNum, " +
" sum(act_property_id >= 5 and act_property_id <> 102) as validReportNum, " +
" sum(act_property_id >= 2 and act_property_id <> 102) as operateNum, " +
" sum(act_property_id > 4) as instNum, " +
" sum((state_id = 9) or (state_id = 4 and act_property_id = 102)) as cancelNum, " +
" sum(act_property_id = 101) as archiveNum " +
" from to_pub_report " +
" where 1=1 " +
reportExtendSql +
" group by user_id
sum（case when end）支持mysql、oracel、达梦的
sum(case when id > 0 then id end CASE) 如果id大于0，则取id值进行求和
select user_id, " +
" sum(case when state_id > 0 then 1 end CASE) as reportNum, " +
" sum( " +
" case when act_property_id >= 5 and act_property_id <> 102 then 1 else 0 end) as validReportNum, " +
" sum( " +
" case when act_property_id >= 2 and act_property_id <> 102 then 1 else 0 end) as operateNum, " +
" sum(case when act_property_id > 4 then 1 else 0 end) as instNum, " +
" sum(case " +
" when (state_id = 9) or (state_id = 4 and act_property_id = 102) then 1 " +
" else 0 end) as cancelNum, " +
" sum(case when act_property_id = 101 then 1 else 0 end) as archiveNum " +
" from "+SchemaConst.DLMIS_+"to_pub_report " +
" where 1 = 1 " +
reportExtendSql +
" group by user_id
最新文章
- 从零开始编写自己的C#框架（28）——建模、架构与框架
- redis-cli中那些或许我们还不知道的一些实用小功能
- webSphere内存溢出
- JSON对象和字符串之间的相互转换
- Linux set env export declare unset
- 主机找不到vmnet1和vmnet8
- 虚拟机 Linux 系统增加换页空间
- Session技术详解
- Android 自定义UI--指南针
- android学习ScrollView的使用
- Linux定义变量的脚本
- 错误代码： 1045 Access denied for user 'skyusers'@'%' (using password: YES)
- OpenLayers3的轨迹回放
- SQL查询【根据生日计算】
- PHP操作cookie
- [NIO-1]缓冲区
- mongodump and mongorestore
- 3.4 unittest之装饰器（@classmethod）
- Two Melodies CodeForces - 813D (DP,技巧)
- LG1116 【车厢重组】