常用批量增删改查sql处理案例

最新推荐文章于 2024-08-17 23:04:19 发布

重生之it界大佬

最新推荐文章于 2024-08-17 23:04:19 发布

阅读量227

点赞数

文章标签： sql 数据库 sqlserver

本文链接：https://blog.csdn.net/weixin_45650629/article/details/130243135

版权

批量新增sql案例：
insert into 表名（字段名1,2,3...）(select 字段1,2,3... from xxx where xxx)

批量修改sql案例：
UPDATE t_hospital_depart_brand_history t2 ,t_hospital_depart_brand t1 SET t2.histroy_flag = t1.histroy_flag
where (t1.institutionno=t2.institutionno and t1.depno=t2.depno and t1.medicineno=t2.medicineno) and t2.histroy_flag != t1.histroy_flag

批量修改sql案例：
UPDATE t_hospital_depart_brand SET month =
		CASE
        month 
        WHEN '2023年3月' THEN
        '2023年03月' 
        WHEN '2023年4月' THEN
        '2023年04月' 
    END 
		where (month = '2023年3月' or month = '2023年4月')


批量删除sql：
delete from `t_hospital_depart_brand` where id in(
SELECT a.id FROM (SELECT id FROM (
SELECT * FROM t_hospital_depart_brand t1 WHERE ((SELECT COUNT(*) FROM t_hospital_depart_brand t2 WHERE t1.institutionno=t2.institutionno and t1.depno=t2.depno and t1.medicineno=t2.medicineno and t1.month=t2.month) > 1)
) t3 where updtimestamp=(SELECT min(updtimestamp) from t_hospital_depart_brand as t4 WHERE t3.institutionno=t4.institutionno and t3.depno=t4.depno and t3.medicineno=t4.medicineno GROUP BY  institutionno,depno,medicineno)
)a
);

只查重复数据sql：
SELECT * FROM t_hospital_depart_brand_history t1 WHERE ((SELECT COUNT(*) FROM t_hospital_depart_brand_history t2 WHERE t1.institutionno=t2.institutionno and t1.depno=t2.depno and t1.medicineno=t2.medicineno and t1.month=t2.month) > 1)

查询重复数据中修改时间小的那个
SELECT * FROM (
SELECT * FROM t_hospital_depart_brand_history t1 WHERE ((SELECT COUNT(*) FROM t_hospital_depart_brand_history t2 WHERE t1.institutionno=t2.institutionno and t1.depno=t2.depno and t1.medicineno=t2.medicineno and t1.month=t2.month) > 1)
) t3 where updtimestamp=(SELECT min(updtimestamp) from t_hospital_depart_brand_history as t4 WHERE t3.institutionno=t4.institutionno and t3.depno=t4.depno and t3.medicineno=t4.medicineno GROUP BY  institutionno,depno,medicineno)