不常用高效sql基本语法和实际使用

本次文档总结一下不常用sql的语法和实际使用场景

1.mysql 分组后 行转列

语法 ： GROUP_CONCAT(DISTINCT 需要组合的字段 SEPARATOR ',') as data

测试案例

SELECT

GROUP_CONCAT(

DISTINCT data_type SEPARATOR ','

) list,

data_ident

FROM

t_ndri_crane_basicsconfig

GROUP BY

data_ident

示例数据

 

 

使用案例

2.结果集 行拼接

语法：concat(str1, str2,...) 如果拼接的列中又null 则拼接的结果为null

 

实例测试

3.以上基础下同类语法

与前面的concat（）语法类似 也是将结果集合并

不过一次指定了 所有数据相见分隔符 separator 就是指定合并中间符 ‘，’ 如果是null 就全部展示为null

语法：concat_ws(separator, str1, str2, ...)

4.merge into

Mybatis批量更新，批量插入 foreach merge into的用法

<update id="insertArea" parameterType="java.util.List" useGeneratedKeys="false">
        MERGE INTO 表名 t1
        USING
        (
        <foreach collection="list" item="item" index="index" separator="union">
            SELECT #{item.locationNo,jdbcType=VARCHAR} as LOCATION_NO, #{item.id,jdbcType=VARCHAR} as ID FROM dual
        </foreach>
        ) t2
        ON (t1.location_no = t2.location_no)
        WHEN NOT MATCHED THEN
        INSERT (ID,PROJECT_NO,stage,LOCATION_NO,SEGMENT_TYPE)
        VALUES(t2.ID,#{item.projectNo,jdbcType=VARCHAR},#{item.stage,jdbcType=VARCHAR},t2.LOCATION_NO,'2')
    </update>

merge into 是Oracle9i 引入的语法

如果有相同数据 就做修改 如果没有就新增 之类的操作

基础语法为

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 

5.Oracle 分组排序后取各组的第一名

select * from
(
select t.*, row_number() over (partition by data_type order by producedate desc) rn  from 表名 t
)
where rn=1

 

关键函数

row_number() over (partition by data_type order by producedate desc)

over 取某一列 分组 再取某一列进行排序 配合 row_number() 按分组排序设置每条数据的rn值

最外层放个where条件 取每组的第一条

网上资源解释为

over()函数写法over(partition by expr2 order by expr3)，根据expr2对结果进行分区，在各分区内按照expr3进行排序； 

over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum()等配合使用。

分区partiton by 与 group by的区别 

group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到； 

partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。