Sql开发笔记

1、做正式sql语句处理前，先在测试环境执行sql语句，看看有没有问题

2、如果在不同的数据库间切换，sql语句的表名前面记得加上数据库名，避免在错误的地方执行

3、复杂的数据更新，最好sql能看到要更新的内容，比如下面的select语句

1
2
3
4
5
6
7
8
9


update xdp_course_ware_topic_discussion c join 
(
select a.id from xdp_course_ware_topic_discussion a, 

(select max(id) id, customer_id , ware_id from xdp_course_ware_topic_discussion where del_flag = 0 
group by customer_id, ware_id having count(*) > 1) b

where a.customer_id = b.customer_id and a.ware_id = b.ware_id and a.id != b.id and a.del_flag = 0 ) d on c.id = d.id 
set del_flag=1, update_time = 1618848000

4、复杂的sql语句先理清楚逻辑。比如课程-单元-课件结构，求单元完成数。

思路：总的单元数下的课件数与已经学过的单元数下的课件数做比较。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


select count(distinct item_id)
from
(select a.item_id,count(distinct ware_id) as num from fxy_learn_log  a 
join chinese_course_items b on a.item_id = b.id 
join chinese_course_ware c on a.ware_id = c.id 
where a.course_id = 651
and b.delete_time = 0
and c.delete_time = 0
and b.type = 0
and customer_id = 88981
group by a.item_id
)a 
join 
(
    select a.id,count(distinct b.id) as num from chinese_course_items a 
    join chinese_course_ware b on a.id = b.chinese_item_id 
    where a.course_id = 651
    and a.delete_time = 0
    and b.delete_time = 0
    and a.type = 0
    group by a.id
)b 
on a.item_id = b.id
where a.num = b.num

Contents