开发常用sql句式记录

1.按属性统计，并且将不存在的属性统计为0

左连接+count函数。因为count函数不统计null。所以要count（右表属性）

on是左连接时的一次筛选

where是连接之后的一次筛选 

postgre示例：

        select count(e.id) as cnt,d.name as type from district d LEFT JOIN examination_application e
        ON ST_Intersects(d.position,e.dig_position)
        <if test="startTime!=null">
            and
            e.create_time &gt;= #{startTime}
        </if>
        <if test="endTime!=null">
            and
            e.create_time &lt;= #{endTime}
        </if>
        and e.examine_status_code!='CG'
        GROUP BY d.name

2.sum()函数默认转换成java的BigDecimal，count()函数默认转换成java的Long

3.MYSQL提取分类再导入分类表

insert into emergency_supplies_point_v2(subordinate_unit,warehouse_name,reserve_type,temp) 
select e.subordinate_unit,e.warehouse_name,e.reserve_type,e.temp FROM emergency_supplies_v2 e where e.id in(
SELECT max(d.id) FROM `emergency_supplies_v2` d group by warehouse_name)

update emergency_supplies_point_v2 set lon=(SELECT
SUBSTRING(temp FROM 1 FOR INSTR(temp, ',')-1)) 

update emergency_supplies_point_v2 set lat=(SELECT
SUBSTRING(temp FROM INSTR(temp, ',')+1)) 

update emergency_supplies_v2 e set point_id=(SELECT
d.id from emergency_supplies_point_v2 d where d.warehouse_name=e.warehouse_name) 

4.多列唯一值去重

查询重复：

SELECT max(id),jcxdm,jcsbsj FROM monitor_data_history GROUP by jcxdm,jcsbsj HAVING count(1)>1

去重：（速度慢）

DELETE from monitor_data_history a 
where a.id not in (SELECT max(id) FROM "public"."monitor_data_history" GROUP by jcxdm,jcsbsj)