sqlserver 知识点积累

1.mybatis在只传入一个参数的情况下，并且该参数不为空，需要用‘_parameter’参数值来代替，否则值取到的会不一致

<select id="courseMenuTreeNode" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from edu_classification where 1=1
        <if test="_parameter != null">
            and parent_id = #{pId,jdbcType=INTEGER}
        </if>
    </select>

2.手动插入含有主键的表数据，需要用到标识列去解决，并且该标识列是成对出现

  1).SET IDENTITY_INSERT 表名 ON ：表示开启对标识列显示值插入模式，允许对标识列显示值进行手动插入数据。

  2).SET IDENTITY_INSERT 表名 OFF：表示关闭对标识列显示值的插入操作，标识列不允许手动插入显示值

set identity_insert tmp_file on;
INSERT INTO tmp_file ([file_url], [file_type], [content_type], [md5], [file_size], [file_name], [user_id], [create_time], [id]) VALUES ('https://baidu.jpg', 'jpg', 'image/jpeg', 'adminqwertyuiop', '23', 'admin.jpg', '129', '2019-07-23 17:13:39.900', '456');
set identity_insert tmp_file off;

3.手动列转行，在不使用关键函数的情况下，运用sum()求和函数进行转换

将该列数据转换为行内显示

select 
CONVERT(varchar(100), a.create_time,23) create_time,
sum((case when a.total_type='endBalance' then a.person_account end)) endBalance,
sum((case when a.total_type='startBalance' then a.person_account end)) startBalance,
sum((case when a.total_type='assignPerson' then a.person_account end)) assignPerson,
sum((case when a.total_type='balanceToCash' then a.person_account end)) balanceToCash,
sum((case when a.total_type='inAccount' then a.person_account end)) inAccount,
sum((case when a.total_type='shopping' then a.person_account end)) shopping
from
tmp_report_account_summary a 
group by 
CONVERT(varchar(100), a.create_time,23);

通过sum函数可以有效规避不需要分组的序列.....我目前得到的结论哈哈

但是我这边准备后期做成动态sql,根据不断增加的业务类型自动统计，等后期需求提吧

4.select和update的互相配合调用，穿插正则和其他属性

-- 1.根据正则和长度判断username是否存在
-- 2.然后和主表关联，修改查询出的字段，这样可以直接一条sql解决问题
update mj_vendor_user as a1
join
(select user_name from mj_vendor_user where customer_no is null and LENGTH(user_name)=8 and (user_name REGEXP '[^0-9.]'=0)) a2
on a1.user_name = a2.user_name
set a1.customer_no = a2.user_name;

5.一些小技巧

• 时间格式转换 

CONVERT(varchar(100), getdate(),23);------2019-07-31

 

• 字段类型转换，对相关int类型转为string类型做处理

(convert(varchar(128),t.first_type) + '/' + convert(varchar(128),t.second_type)

• in not in的运用---可以在delete删除，select查询，update修改中使用，性能问题谨慎使用

 

对工作中用到的一些sql进行积累，常备无患