达梦、oracel、mysql数据库兼容

联合表更新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